SQL Basic Maintenance Script

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: