From The Mailbag: DBCC CHECKDB And Read Only Databases

We got an email!!!

It was weird. It was from an Australian, and it was right side up. I could hardly believe it. Normally we encourage people to ask us questions during Office Hours, or over on Stack Exchange. That way lots of people can benefit from the answer.

But since this reader is on the other side of the globe, they only get to listen to Office Hours two days before it’s even recorded. Time is weird like that.

Titular Line

So, can you run DBCC CHECKDB on a read only database? Should you run DBCC CHECKDB on a read only database?

tl;dr: YES AND YES!

Here’s why:
Many forms of corruption that I’ve seen have come from storage. Sure, there have been bugs that were to blame, but yeah. Most of the time, it’s the storage going all yucky.

Granted, if a read only database becomes corrupt, it’s fairly easy to just restore the last good full backup. Unless it’s a really big database. Then it could take some time to restore. That’s RTO — you won’t lose any data here — but it’s generally more courteous to find corruption on nights and weekends rather than let users find it during the day when they try to run a query.

Another reason you may want to do it, is CHECKUMS. If you’re a good little SQL owner, you have alerts set up to email you when some forms of corruption are detected. These alerts rely on verifying page checksums as they’re read from disk. If you made your database read only before a page got a checksum, SQL may never know. Checksums only get applied when pages are written to disk. You can imagine that some rarely-used tables may not have had that happen in a while, and may just be sitting on some storage blocks that went bad.

It’s also entirely possible that the corruption isn’t just limited to one database 😀

It could be all of them 😀

Are there any gotchas?

Not really, but you should be aware of a couple things. While CHECKDB will run fine, it won’t update flags inside the database to tell you it did.

That means sp_Blitz may warn you that CHECKDB is out of date. Realistically, that’s not going to change. You could be using Ola Hallengren’s scripts, you could be using Minion Ware, you could be using Maintenance Plans, or some other 3rd party tool or script. You could even be running them from another server that just gets used to run Agent jobs across your environment. We couldn’t possibly, sanely, account for all the possibilities. There are some real wackadoos out there.

Fortunately, sp_Blitz lets you skip certain checks, and even skip certain checks for certain databases. If it bugs you, look into that. If not, just make sure you check the logging of whatever you use to run CHECKDB to make sure it’s happening. For instance, Ola recently added a pargumeter called Updateability to let you choose to run or not run certain tasks on read only databases. The default is both, but who knows? Like I said, real wackadoos out there.

Thanks for reading!