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:

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   

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.