How To Shrink Database Of Acumatica With Rebuilding Of


Hello everybody,

today I want to leave another portion of SQL Server optimization for Acumatica database. In case if your db become huge, and you want to make it smaller, you can try following SQL code:

PRINT N'Shrinking database files'
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 
EXEC (@Command)  
OPEN TableCursor   
FETCH NEXT FROM TableCursor INTO @Table   
    PRINT 'Rebuilding all indexes on ' + @Table    
    SET @Command = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD'
    EXEC (@Command) 
FETCH NEXT FROM TableCursor INTO @Table   

this code does two steps:

  1. Shrink database
  2. Rebuild indexes

Remember for yourself forever that any shrink will destroy your indexes. So each time you decide, or risk to make shrink your database don't forget to rebuild indexes. Otherwise you'll be hugely surprised to discover that also db is small, but performance is terrribly bad.

Just want to say that I've tried that approach on db of one of mine customers and results were the following: 646 Gb got shrinked to 161 Gb. Pretty impressive, huh?

And of course, word of warning. Never try that SQL, without recent back up of your production.

Comments are closed