I'm trying to compose T-SQL script that will:
A) Shrink database size to the minimum possible size; B) Make integrity checks, and completely "reorganize" and optimize the database so that it is in a "fresh" state.
Here is what I currently have:
-- Check database integrity: DBCC CHECKDB WITH NO_INFOMSGS; GO -- Get space usage information: EXEC sp_spaceused @updateusage = N'TRUE'; GO -- Shrink database: DBCC SHRINKDATABASE (0, 0); GO -- Reindex all tables: EXEC sp_msforeachtable N'PRINT ''Indexing table: ?''; DBCC DBREINDEX (''?'');' GO -- Update statistics for all tables: EXEC sp_msforeachtable N'UPDATE STATISTICS ? WITH FULLSCAN' GO -- Clear procedure cache: DBCC FREEPROCCACHE; GO -- Update all usage info in the database: DBCC UPDATEUSAGE (0); GO
Can you please validate if all these are correct? The precedence of commands (e.g. updating statistics after index rebuild, or vice versa), syntax, arguments, etc. are all optimal? Can you suggest any other commands? Performance is not a problem, I'll be executing this script probably once in a month, night hours, so even if it takes hours it doesn't matter. I just need the maximum optimization of the database. Any input would be greatly appreciated.