Offloading DBCC CHECKDB

I recently had a discussion about the ability to offload DBCC CHECKDBs to a secondary database using Active Secondaries in SQL Server Availability Groups. While it is fully possible to run database consistency checks against secondary database (and there’s plenty of recommendations floating around for doing this), it needs to be pointed out that using Availability Groups for this does not equal of running checks on the primary database.

Let’s consider this for a bit…

The problem

The main problem I see with using Availability Groups as a way to offload DBCC CHECKDB comes from the nature of database corruption itself, it’s almost always (there are some other causes too, but much more rare) caused by an error in I/O subsystem. The changes related to corruption that happen at the storage level are not replicated to secondary database, as Availability Groups use log blocks for replicating only changes done by actual SQL Server transactions at the primary. So whatever is wrong in the datafile at the primary, it will not be at the secondary.

Then how do you offload DBCC CHECKDB?

The only way I know how to do this, is to take a FULL BACKUP of the database you are checking, copying it somewhere and running the CHECKDB against that one. That backup will contain also the actual corruption, if any, present at the primary. Of course if you have a huge database and that’s the reason you are looking to offload your database checks, then you might have some thinking to do how to exactly perform the BACKUP and RESTORE routine in a timely manner.

But.. What about those secondaries?

At this point you are probably wondering that if you cannot detect storage corruption at the secondary, then why would you offload the CHECKDB there? That is actually a very good question, and after thinking about this for a good while, I can only think of a one reason you might do that.

“Because then you will know that you have a copy of a database that is not corrupted.” 

Sure, it’s not your primary database, but in Availability Group failing over to non-corrupted copy shouldn’t be a big of an issue. Also there are some nice features in SQL Server these days that will still work to your benefit when dealing with certain types of corruption in a primary database, such as the Automatic Page Repair.

However there are still few things you do need to worry about, especially the backups. Your full backup, which you take from the primary database (as you can only have COPY ONLY backups from the secondary) would contain that data corruption as well. If you are never testing against a database restored from that FULL BACKUP, you might have corruption in your database going back quite a long while.

Should I offload it?

If you have a such gigantic database that you cannot complete your DBCC CHECKDB in a reasonable timeframe, then you probably need to. Depending on how business critical your database is, should be used to define wether it is enough knowing you have an uncorrupted copy, or if you should be doing the restores and running against that database instead.

If you don’t have issues running DBCC CHECKDB on you production box, then it might be better to leave it running there and re-visit the topic if it at some point becomes an issue. The one recommendation I can give, and that doesn’t involve “it depends”, is the following:

Always run DBCC CHECKDB and as often as you possibly can.

Hopefully after reading this post you are at least thinking about checking on how you are running DBCC CHECKDB currently (you are running these checks, right?).

Thank you for reading the post!

 

Author: Mika Sutinen

Hi, My name is Mika Sutinen and I'm a Lead Database Administrator and a Data Platform Product Owner for a company called Tieto. I've been working in IT-industry for two decades, and I've spend most of my career working with healthcare information systems. I've worked with SQL Server for most of my career, starting with version 6.5 a long, long time ago. My other interests are high availability, everything related to performance (testing, monitoring, etc), Windows operating systems and I'm currently learning more about Azure.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.