I came across a database that has suffered a lot of deadlocks recently. This, along with slow query execution among other problems led me to convince management that the database required downtime. Some of the maintenance I performed was re-building tempdb tables and moving them off the C Drive, which I highly recommend for various reasons. You can read more on that here.
After analyzing the entire database, I determined I needed to accomplish a few things:
- Run a DBCC check/rebuild
- Scan through each table and run a CheckTable
- Re-index each table
- Rebuild the allocation structures
- Update the statistics
My co-worker and I built a script using cursor methods to run while we went to sleep one late night. We restored a recent backup to a new database and tested the script there. The total time took roughly 6 hours, so we figured we would sleep while running the script on the production server. Finally, we made that the database would be in single-user mode so any jobs, triggers, etc. would not run while we fix the database. The code we came up with is below:
EXEC sp_resetstatus 'YourDatabase'; ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE DBCC CheckDB ('YourDatabase', REPAIR_REBUILD) -- repairs database -- Below will reindex all the tables DECLARE @TableName varchar(255) DECLARE TableCursor CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_type = 'base table' and table_name <> 'audit_log_blobs' and table_name <> 'audit_log_data' and table_name <> 'audit_log_transactions' and table_name <> 'audit_tables' and table_name <> 'ARCHIVE_AUDIT_LOG_DATA' and table_name <> 'ARCHIVE_AUDIT_LOG_TRANSACTIONS' OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'Checking integrity of ' + @TableName -- integrity structure DBCC CHECKTABLE(@TableName, REPAIR_REBUILD) PRINT 'Reindexing ' + @TableName -- reindex table DBCC DBREINDEX(@TableName,' ',90) FETCH NEXT FROM TableCursor INTO @TableName END CLOSE TableCursor DEALLOCATE TableCursor DBCC CHECKALLOC ('YourDatabase', REPAIR_REBUILD) -- allocation structures EXEC sp_updatestats -- statistics ALTER DATABASE [YourDatabase] SET MULTI_USER -- returns to database to normal