Maintaining database indexes
To ensure better performance and prevent database corruption, Centrify 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:
=== BEGIN SQL statements === 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 FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','msdb','tempdb','model') ORDER BY 1
OPEN DatabaseCursor FETCH NEXT FROM DatabaseCursor INTO @Database WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'Processing database ' + @Database SET @Command = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + TABLE_CATALOG + ''].['' + TABLE_SCHEMA + ''].['' + TABLE_NAME + '']'' as TableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE''' EXEC (@Command) OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'Rebuilding all indexes on ' + @Table SET @Command = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD' EXEC (@Command) FETCH NEXT FROM TableCursor INTO @Table END
CLOSE TableCursor DEALLOCATE TableCursor
FETCH NEXT FROM DatabaseCursor INTO @Database END CLOSE DatabaseCursor DEALLOCATE DatabaseCursor === END SQL statements ===