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 ===