How Often Do You Run CHECKDB Against Your Databases?


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.

4 thoughts on “How Often Do You Run CHECKDB Against Your Databases?

  1. Great article. Even on those very large DB’s, if you can split the data into multiple filegroups then you can run dbcc on each filegroup if you are concerned the dbcc checkdb will take to long to run on the entire database. On most of my VLDB’s I split the historical database tables into other file groups. That data never changes so if by some chance it gets corrupted then any backup before the corruption would be ok. However the primary file group where data is constantly changing has dbcc ran daily. In some case this is possible, others it is not. As always, “it depends”. It is surprising how many dba’s are ok with dbcc checkdb weekly.

  2. Brad,

    A useful reminder for all DBAs!

    Your suggestion of running DBCC CHECKDB against restored copies of production DBs on non-production environments (instead of directly against production DBs in production environments) has another side benefit: confirming that the backup media is intact (not corrupted) and will restore properly – another useful DBA task to perform on a periodic basis.

    Scott R.

  3. Great article and great comments, really made me think about implementing this as a daily task. But because it is so intensive it makes me consider offloading it onto a non-production box as suggested.

    I guess the best way to do this as commented by Scott R is to replay the backups nightly onto a non-production box and schedule a DBCC CHECKDB on that box. I like the benefit of testing out the backups too.

    If i set up a box specifically to test out backups and to run DBCC CHECKDB on what would be the licensing implications on this box? Would i still require a SQL license for this?

Comments are closed.