Maintaining Database Indexes

To ensure better performance and prevent database corruption, Delinea recommends you rebuild the database indexes for all the audit store databases and the management database as a regularly scheduled task that your run at least once a week. Rebuilding the indexes is especially important for the active audit store database to reduce fragmentation, but as a best practice you should rebuild indexes for all attached databases and the management database.

The following sample SQL statements illustrate how to rebuild all indexes on all the databases in one script:

Copy

DECLARE @Database NVARCHAR(128)
DECLARE @Table NVARCHAR(128)
DECLARE @Command NVARCHAR(500)
-- To skip index rebuilding for a database, add its name to the list below
DECLARE DatabaseCursor CURSOR FORSELECT name FROM master.dbo.sysdatabasesWHERE name NOT IN ('master','msdb','tempdb','model')ORDER BY 1
OPEN DatabaseCursorFETCH NEXT FROM DatabaseCursor INTO @DatabaseWHILE @@FETCH_STATUS = 0BEGINPRINT 'Processing database ' + @DatabaseSET @Command = 'DECLARE TableCursor CURSOR FOR SELECT''['' + TABLE_CATALOG + ''].['' + TABLE_SCHEMA
''].['' +TABLE_NAME + '']'' as TableName FROM [' + @Database'].INFORMATION_SCHEMA.TABLESWHERE TABLE_TYPE = ''BASE TABLE'''EXEC (@Command)OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableWHILE @@FETCH_STATUS = 0BEGINPRINT 'Rebuilding all indexes on ' + @TableSET @Command = 'ALTER INDEX ALL ON ' + @Table
' REBUILD'EXEC (@Command)FETCH NEXT FROM TableCursor INTO @TableEND
CLOSE TableCursorDEALLOCATE TableCursor
FETCH NEXT FROM DatabaseCursor INTO @DatabaseENDCLOSE DatabaseCursorDEALLOCATE DatabaseCursor