Last weekend I attended SQL Saturday in Chicago.
How do I get all my SQL server healthy?
During one of the sessions there was a brief discussion about DBCC CHECKDB. The long and short of it: It is important, do them, it is not an option to skip this check. It is right up there with Index maintenance and updating statistics. But, no one ever said that you had to run them on your production database. I repeat, no one said you had to run them on your production database. Here’s a couple of thoughts. First when you do your weekly backup/restore test, notice I didn’t say ‘if’, run the DBCC CHECKDB against your restored copy. If you have inconsistencies in your production database, it will also exist in an exact copy of you database. But remember to fix it on your production database and not you copy that you will drop later. Another option is if you use backup/restore to create a report server. This is an excellent way to do the checks and verify your backups are good. We have a couple of systems that we do this every night when the SQL backup is complete, we restore it the report server. Proved the backups are good. I then look for a time that Crystal Info is not pounding the report server and run the dbcc checks against that database copy. I have it e-mail me if it finds any errors. Kills two birds with one stone. Now for the truth in my shop. With over 120 instances and over 1600 databases, a weekly backup test and DBCC CHECKDB is just not practical. So I use a hybrid approach. The servers that refresh a report server daily are easy perfect candidates, our larger (high priority) databases get a restore to a test server and DBCC CHECKDB there, and the other databases get an occasional backup/restore test and DBCC CHECKDB run against the production database once per week during slow times. So think outside the box. Find a way to verify your database’s consistancy.
Dep
Tags: Database, SQL Server