DBCC CHECKDB: Look for SQL Server Corruption

A monster’s worse fear is being found.

– Richelle Goodrich

This is part of the Two Weeks to a healthier SQL Server series. Today we’ll address SQL Server Corruption – or hopefully how to find and deal with it before it finds and deals with you.

Data loss and user deletes are expensive and difficult. site crashes hurt. losing your infrastructure isn’t fun. There are a number of visible problems that hurt you – and they are pretty bad. But, at least those problems usually “announce” themselves (sometimes in a really smelly, and obnoxious voice.)

What about SQL Server database corruption? Are you clean here? How do you know? This can be a problem right now that you just don’t know about.

Corruption, you see, only shows up if you touch a page of data that is corrupt. For some of you that may mean only once a year at annual accounting close for that transaction from last May. For others, it may only be that one time the client calls and you need to pull up their history. That’s super scary. It wasn’t the monsters I was scared of as a kid. It was not knowing if they were there.

“Wait, Mike, there are monsters potentially in my SQL Server databases?!?! And I won’t know until someone finds them?!?!?” – Sort of, yes. A good parent will help hunt for the monsters a time or two, all you need is a good flashlight (and, yes, some patience.). While there are monsters potentially out there. Let me give you a flashlight…

You can make SQL Server review each page of data looking for corruption and giving you a heads up. If that monster is there, you will find it. If you get a clean bill of health, it was just the furnace that you heard, go back to sleep – soundly. If you do this frequently enough and keep the right number of backups (Corruption resistant backup strategy) – you can not only recover corrupted data in many cases, but you can get your environment online with no data loss, perhaps sacrificing a little up time for the restores. And you can do this yourself. Right now. If you aren’t doing it, and we come in to do a SQL Server health check, we’re going to make that a critical finding. So start using the flashlight.

DBCC CHECKDB – Look for SQL Server Corruption

You should hunt out the corruption in your environment. A good SQL Server maintenance strategy includes many things. We talked about backups in the last two weeks to a healthier SQL Server series post, and Ola Hallengren’s maintenance solution all over this blog. Another item, though, is running database integrity checks. Or DBCC CHECKDBs against your databases. And don’t forget the system databases – they’re databases, too.

A SQL Server Corruption Resistant Backup Strategy

I have a (really long) blog post that discusses what I call a SQL Server corruption-resistant backup strategy. In short – corruption usually lives in the data file (Though you can get log file corruption and can have corrupted backups, which is why you need to test your backups through frequent restores and verify them when you take them… As discussed in the backup post.)

The really short version of that long post is:

• Run DBCC CHECKDB frequently (as frequently as you can, we generally do weekly)
  • Review the output. It is’t enough to just run the checkdb. You have to alert on job failures or read the output when it errors!
• Keep your databases in FULL Recovery Model
• Take Frequent Log Backups
  • … And keep them around between checkdbs *Until you’ve reviewed the CHECKDB*
  • Keep at least the last good full (Good checkdb, then a full? Keep that, maybe keep the one the week prior) and ALL log backups since.
• If you have corruption – call someone. Like us. (See below)

What happens if you find SQL Server database corruption?

I don’t say this lightly. In fact, it goes against every fiber of my being to say what I’m about to say – but step one is “Get paid help.” I hate saying that – because I don’t want to be shilling for a company like mine on the blog. It doesn’t have to be me. It can be any of the great companies out there. It can be Microsoft. But – the first step is truly – Step away from the keyboard. Step away from Google. Step away from SSMS.

Unfortunately, I see SQL Server database corruption a lot. Quite a few partners have seen us as experts there – so they send all of their corruption problems our way. And when we manage the database estates for over 100 SQL Server DBA as a Service customers using Straight Path, the law of averages says that we’ll see it a lot more than a sole DBA at one company for twenty years.

And even still – we have to evaluate each corruption independently. And even still – we see a situation that makes us say, “What the heck is going on there, let’s call our friends like Paul Randal or Microsoft to ask if they’ve seen this one before.”

Some corruption can be recovered easily. Just drop and rebuild an index. Some can be recovered with tricks. Some can be recovered with a safer repair command. Some can’t ever be recovered, but the good data can be salvaged. Sometimes even that can be difficult. But – with a corruption-resistant backup strategy – I’ve not yet bumped into the corruption that cannot be recovered with a restore script…

And I’ve seen database corruption that cannot be recovered ONLY BECAUSE of all of the terrible advice on the internet.

This is perhaps the ONE SITUATION – that I beseech you to call for help. Whatever the cost – it’s probably less than the value of your data.

Homework

• Review ALL databases for corruption. NOW. Run checkdbs (CheckDBs are online, but it will steal resources – so run it off hours. And you’ll need space for the snapshot that checkdb creates to allow it to be an online operation.)
• Get this job running on a schedule
  • System DBs – daily is fine
  • User DBs – as frequent as you can, but at least weekly, keep log backups throughout
• Go back and read the backup post. And make sure you have a corruption resistant backup strategy as defined here in this post.
• IF YOU FOUND CORRUPTION. STOP. BREATHE. And remember:
  • It is what it is.
  • You at least looked for it. And saw – there is a monster. Now we can deal with it.
  • Don’t try and fix it if you haven’t.
  • Get help. Heck – just e-mail me from the site and at least let me talk you off the cliff and give you at least one e-mail worth of advice on my dime.
• If these posts are helping you – drop me a line and let me know so I know to kepe sharing. Tell me what you learned. Tell me what’s changed. Ask me a quick question. If they aren’t, tell me. I want to hear from you – mike@straightpathsql.com