Tips for finding Knowledge Articles

  • - Enter just a few key words related to your question or problem
  • - Add Key words to refine your search as necessary
  • - Do not use punctuation
  • - Search is not case sensitive
  • - Avoid non-descriptive filler words like "how", "the", "what", etc.
  • - If you do not find what you are looking for the first time,reduce the number of key words you enter and try searching again.
  • - Minimum supported Internet Explorer version is IE9
Home  >
article

KB-8472: How to shrink SQL server Database and rebuild indexes

1 April,17 at 04:43 AM

Applies to:
All versions of Centrify DirectAudit on all platforms

Question:
In a situation when purge sessions did not help to release disk space on SQL server, how do we shrink the database and rebuild the indexes to release disk space?

Answer:
Shrinking the DB and rebuilding the indexes should help to recover the disk space.  Before you run the queries to shrink the DB, check with the Database Administrator to make sure the database is in simple recovery mode. 


It is recommended to run the following queries:

DECLARE @Table NVARCHAR(128)  
DECLARE @Database NVARCHAR(128)
DECLARE @Command NVARCHAR(500)

PRINT N'Shrinking database files'
DBCC SHRINKDATABASE(0)

PRINT N'Rebuilding all indexes'
SET @Database = DB_NAME()
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

NOTE:
Make sure the user running script has sysadmin priviledge on the database server and since the script may take a lot of time to finish, it's recommended to run this script during off-hours or on the weekend.

Attached to the KB is the ShrinkDB_RebuildIndexes.sql file which has SQL statements above.  

Still have questions? Click here to log a technical support case, or collaborate with your peers in Centrify's Online Community.

Related Articles

No related Articles