Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

How Often Do You Run CHECKDB Against Your Databases?

image

I recently ran a poll, asking DBAs how often they ran DBCC CHECKDB against their databases. Compared to some of the polls I have run recently, I was somewhat pleasantly surprised by the results.

My first pleasant surprise was that at least 90% of those polled knew what DBCC CHECKDB was. This is a good sign that most DBAs have at least a basic understanding of database maintenance best practices.

Of the 90% of the DBAs who run DBCC CHECKDB against their databases, just about 27% of them do it on a daily basis. These must be very diligent DBAs who take their roles of “protectors of the organization’s data” very seriously, and I commend them.

On the other hand, the largest number of poll takers, about 40% of them, indicated that they only perform this operation weekly. These DBAs are making the assumption, that under the worst case scenario, that is acceptable to loose up to a week’s worth of their data. This is because database corruption could occur sometime between the weekly DBCC CHECKDB checks, and not be detected until the next weekly run. During this time, the corruption, if not discovered, could become part of their database backups. This means that even if the database is backed up daily, and transaction made logs regularly, that if the backups contain the corruption, and the corruption is not fixable, then they potentially might have to restore their last good backup, which in the worst case, could be one week ago.

About 10% of the DBAs responded that they run DBCC CHECKDB monthly, meaning that these DBAs are willing to risk losing, under the work case scenario, a months worth of their data.

And while the overall results of this poll were positive, there were still another roughly 19% of the poll takers who said they never run DBCC CHECKDB. Since these DBAs did not respond that they didn’t know what it was, I am assuming that many of these DBAs don’t fully understand the implications of not running DBCC CHECKDB, or that they don’t care about protecting their organization’s data (which I hope is not the case).

Of course, running DBCC CHECKDB is a resource-intensive task, and it is not always possible to run it daily on a production server, especially on very busy and large databases, and the DBA has to weigh the risk of database corruption versus the resource cost of running the command. On the other hand, DBAs can always restore their daily backups onto a non-production server and run DBCC CHECKDB on the restored backup to offload the workload. In fact, this is my personal recommendation, and in most cases is easy to implement. By offloading DBCC CHECKDB to a non-production server, you can not only perform this task daily, you also remove any potential negative performance impact it might have when running on a production server.

Comments

Posted by Jason Brimhall on 2 November 2010

I am with the majority there - weekly.

Leave a Comment

Please register or log in to leave a comment.