Schedule DBCC CHECKDB

Do you schedule DBCC CHECKDB for all of your important databases?

Scheduling database integrity checks ranks right up there with backing up your databases when it comes to glamorous activities in the life of a DBA. That is to say, it’s not glamorous at all.

Integrity checks don’t bring the same internal swagger as say rewriting a query so that it takes 20ms rather than 2 minutes to complete. Scheduling DBCC CHECKDB isn’t as gratifying as creating solid HA/DR architectures, turning logical database designs into physical implementations, or resolving any other complex problem that is causing significant issues for your users.

No, I’d doubt anyone has ever been patted on the back, or had their lunch bought, just because they scheduled DBCC CHECKDB.

At least not before an emergency.

But being able to restore a database that is in good working order, in a timely manner, to the right point in time is a fundamental duty for every DBA. It’s a basic requirement of the job.

And the first part of that, “in good working order,” is what integrity checks are all about.

What happens if you don’t schedule DBCC CHECKDB?

If you’re not running integrity checks regularly, you’re probably going to be ok. At least for a while. SQL Server and storage hardware are pretty good at doing their jobs.

But at some point, a write to disk may get corrupted. Or data pages may be damaged. Maybe it’s a power outage? Or perhaps it’s aging hardware? Or just a glitch that can’t be explained? Regardless of the cause, corruption is suddenly, but silently, introduced into the database.

And the longer the corrupted data pages go undetected, the more difficult the recovery.

The case of a corrupted system table

Recently, a company reached out to us. One of their key software applications couldn’t be patched. The script used to update the database was throwing errors.

As we investigated the issue, we discovered that one of systems tables had been corrupted. Unfortunately, the corruption had gone unnoticed for over six months. That means that every backup from the prior six months was essentially of no use. Backup and restore doesn’t fix database corruption.

We helped them recover from the corruption. But it would have been considerably easier if they’d had a good recent backup.

The moral of the story: regularly test your backups and schedule integrity checks for every database you care about.

Schedule DBCC CHECKDB

Scheduling an integrity check is easy. We’re fans of Ola Hallengren’s SQL Server Integrity Check approach. We use the solution at most every SEROShield client. It’s simple and robust. You can run an integrity check on all user databases with the following script.

EXECUTE [dbo].[DatabaseIntegrityCheck]
@Databases = 'USER_DATABASES',
@LogToTable = 'Y'

Replace USER_DATABASES with SYSTEM_DATABASES and you can check the system databases.

You can also manually create a SQL Agent job to run DBCC CHECKDB on each of your databases, if you wish.

Ok, but what else can you do?

Scheduling DBCC CHECKDB to run regularly is a great start. Here are a few other things you can do to help detect database corruption early.

1. Periodically perform test restores of your databases, and then run DBCC CHECKDB on the restored database. See How Often Should I Test My SQL Server Backups? and How to Test SQL Server Backups Using dbatools.
2. Setup standard alerts and notifications. Specifically, you want to be notified anytime there’s an alert with a severity level greater than 16. You’ll also want to look for error numbers 823, 824, and 825. See Set Up a SQL Server Database Alert (Windows)
3. Make sure to set the PAGE_VERIFY Database Option to CHECKSUM.

Want to know when a database integrity check last completed successfully on your database? See When Was the Last Known Good DBCC CHECKDB Integrity Check?

Want to work with The SERO Group?

Want to learn more about how SERO Group helps organizations take the guesswork out of managing their SQL Servers?

Let’s talk. It’s easy and there is no obligation.