You never know when you’ll encounter corruption. It can happen at any time, usually due to some sort of hardware problem or driver issue. Corruptions don’t disappear and you can’t necessarily
Corruptions can be caused by numerous factors, and it isn’t something you can predict. However sooner or later, you’ll get corruption on one of your instances. I don’t know which one, whether it’s the finance production instance or the vacation tracker development database, but it will happen somewhere.
Since corruption can flow through to backups, and can exist in your system for some time, you could end up losing lots of data. Your best defense is to run DBCC on a regular basis and ensure that you catch corruption or problems as early as possible.
My recommendations in order. If you can’t do #1, do #2, or #3, but try to get something in place.
- Run DBCC on every database, every day – If you can, run this on all your servers. I know it’s a large request for some of you, but if you can do it, do it.
- Run DBCC on every database on another instance, restoring from last night’s backup – If you can’t run DBCC on your main server, offload it onto another one.
- Run DBCC every day on some database, rotating so that all databases are covered – Do this so that at least every week or two you have a DBCC run on your production databases. You can do a random sample of some sort, but hit all of them as often as possible.
- Work on a your resume or CV – If management won’t let you run DBCC checks, sooner or later you’ll have an issue, take some blame, and need a new job. Be prepared for that, if for nothing else.
One of the recommendations to minimize impact from CHECKDB operations is to run them on another server, using an automated process. SQL Backup Pro 7 from Red Gate makes this very easy to do with automated backup file copy operations, restores, and CHECKDB execution.
Filed under: Blog Tagged: Backup/Recovery, Red Gate, sql server, syndicated 
