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:
- Shrink database
- 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.