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