DBCC CheckDB: Frequently Asked Questions
Q: Do I really need to run DBCC CheckDB? Yes. If you’re even partially in charge of a SQL Server and you’d like to keep your job, you should make sure CheckDB is being run regularly. (Seriously!)
Q: But WHY do I need to run CheckDB? CheckDB helps alert you when data becomes corrupt. Data corruption can cause your users to get incorrect data, cause queries to fail, or can even take your entire SQL Server instance offline. Corruption can happen anytime. It could be caused by a problem in SQL Server, an issue with Windows, or an issue with your storage. It could also happen due to other types of software– something like a filter driver (replicating data, defragmenting drives, etc) or a virus scanner.
Q: How often do I need to run CheckDB? Every day you’d like to keep your job, you should run CheckDB. OK, maybe that’s a bit extreme.
Most people run CheckDB against user databases every weekend. That’s “normal”. Is normal good enough for you? Well, maybe not! When availability is important, people choose to run CheckDB more frequently: often once per day.
For system databases, why not check them every night?
Q: How can I make CheckDB faster? First, do you really need to make it faster? If you have a regular maintenance window where decreased performance is OK, that’s often good enough.
The best way to speed up CheckDB is to offload the work to a totally different server. Do this by automating a process where you regularly restore a full backup, then run CheckDB. This approach has multiple benefits:
- Tests your restores
- Doesn’t impact the production database (especially if they’re using independent storage)
- Secondary instance can be configured to use as a “warm standby” in case the primary server fails
And sure, you can potentially offload CheckDB work to a virtualized SQL Server instance. Just make sure that it has enough resources to run CheckDB fast enough.
Q: Are there any shortcuts with LogShipping, Mirroring, or AlwaysOn Availability Groups?
Bad news: None of those technologies mean you don’t have to run CheckDB against your main production database. The only valid way to offload CheckDB is to run it against a restored full backup.
In fact, AlwaysOn Availability groups may mean you probably have to run more CheckDB, not less.
Q: Do some features make CheckDB slower? Yep, some indexes check slower than others. Having nonclustered indexes on computed columns can make CheckDB crawl, as can having indexed sparse columns. If you’re using those features and CheckDB is painfully slow, you might consider disabling the nonclustered indexes before running the job– but that means writing custom code and making sure that you handle any error situations when the job fails. (Kind of gross, right? That could lead to a bad day. Head on back up and see “How can I make CheckDB faster” for a better answer.)
Q: What about “PHYSICAL_ONLY” or “NOINDEX”? Can I use those? Well, you can, but then you might not detect corruption as fast. That could be really bad, right? I’m not a fan of either of these options because they turn off valid features in CheckDB that could really save your bacon. I’d much rather offload CheckDB to another server by restoring a backup as describe above– it has much greater benefits and is even better for performance of your production database.
Q: Should I run CheckDB against TempDB? Yes– you should. CheckDB can’t do every single check against TempDB that it can against other databases (the output will let you know that it can’t create a snapshot), but it can still run a variety of checks against TempDB and it’s supported to run it. Don’t skip it! You should be checking master, model, msdb and tempdb, plus all your user databases.
Q: How can I automate running DBCC CheckDB? You can do this by using the Check Database Integrity task in a Maintenance Plan. You can also use a SQL Server Agent job and a free script to help with database maintenance.
Q: Is CheckDB all I need to protect me from corruption? Running CheckDB can help you find out about corruption, but you also need:
- Page verification settings for databases set to the right level. For anything SQL Server 2005 and higher, you want to use ‘Checksum’ for page verification.
- SQL Agent Alerts configured to let you know if corruption errors strike (even while CheckDB isn’t running).
- The jobs running CheckDB should notify you when they fail
- You need to be prepared! Practice responding to database corruption by learning to cause it, detect it, and correct it in a test environment.
To allow alerts and jobs to notify you, you need to configure Database Mail, enable the database mail profile on the SQL Server Agent (and then restart the SQL Server Agent service), configure an operator, and then set the operator on the alerts and jobs.
Q: What if I find corruption? Corruption happens– that’s why this is so important. The first thing you should do is sign up for our online DBA Interview Training to find that next job. (Awkward laugh.)
All kidding aside (and that was a joke! stop updating your resume!), you must respond to the issue immediately. The first thing to do is to alert others on your team to the issue and make a plan. You need to make sure that a full CheckDB has been run against the database, that you’ve read the output of CheckDB carefully in the SQL Server Error Log.
Gail Shaw has posted an article with a great list of do’s an don’ts when you hit corruption here (login required, sorry). If the database is important to your business, I urge you to spin up a support call to Microsoft Support right away– even while you read Gail’s article. At around $500 USD Microsoft Support is your absolute best value when it comes to getting help right away for corruption. (Yep, I’m an independent consultant and I’m telling you that!)
Once you have the specific incident resolved, make sure you follow up. How long has the issue existed? Can root cause of the original corruption be identified? Could you have the issue again, or somewhere else in your environment? It’s rare for CheckDB to strike once and not repeat itself, so it’s important to follow up.