Problem
SQL Server database corruption can be a problem and can cause serious damage to a database. If you’re an experienced DBA then you probably have safeguards in place to detect this, but over the years I’ve seen hundreds of SQL Servers with no detection methods at all and this is a problem. There are a few ways to detect database corruption, but this tip will focus more on DBCC CHECKDB.
Solution
You may or may not have heard of DBCC (database console commands) statements. These statements are used to perform different operations in your database and can be broken down into four categories: Maintenance, Miscellaneous, Informational, and Validation. I use some of the DBCC statements on a daily basis, but none more than DBCC CHECKDB.
What is SQL Server DBCC CHECKDB
DBCC CHECKDB, from Microsoft MSDN Library, checks logical and physical integrity of all the objects in the specified database by performing the following operations:
- Runs DBCC CHECKALLOC on the database – Checks consistency of disk space allocation structures for a specified database.
- Runs DBCC CHECKTABLE on every table and view in the database – Checks the integrity of all the pages and structures that make up the table or indexed view.
- Runs DBCC CHECKCATALOG on the database – Checks for catalog consistency within the database.
- Validates the contents of every indexed view in the database.
- Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM.
- Validates the Service Broker data in the database
If you’ve ever ran DBCC CHECKDB you know it takes some time for large databases. Now that you know all of the steps that are run, you can see why it takes time to complete.
How can SQL Server DBCC CHECKDB help me?
Data corruption is bad. It can cause all sorts of issues within the database that may include incorrect data results, failed SQL statements, and in some cases can take down the entire SQL instance. DBCC CHECKDB warns you of corruption so that you can fix it before (hopefully) it gets too bad.
How do I use SQL Server DBCC CHECKDB?
DBCC CHECKDB is pretty straightforward. There are a few options you can use with the statement and we’ll go over some of those in the next section, but the basic syntax looks like this:
DBCC CHECKDB ('DatabaseName') Pretty simple.
Automate SQL Server DBCC CHECKDB
Obviously, you don’t want to log in every morning and run this statement on each database, so you can automate this process using a few different methods:
- SQL Server Maintenance plans – Maintenance plans are part of SQL Server out of the box (unless you’re running Express Edition). I don’t like using maintenance plans for the most part, but I don’t mind using them for this type of task. In the Maintenance Plan toolbox you’ll need to use the Check Database Integrity task. The only configurable option is to include indexes so it’s not really user friendly, but in some cases this is all you need. Again, we’ll talk about other options in the next section.
- Custom scripts – Custom scripts are usually what I use and offer the best flexibility as far as adding the options you want. My go-to scripts are already created and free to use from Ola Hallengren. He’s done a wonderful job of creating these and sharing them to the world. Thanks Ola!
SQL Server DBCC CHECKDB Options
There are a few options to use with DBCC CHECKDB and I’ll go over a few of the more popular ones here:
- NOINDEX – Specifies that intensive checks of nonclustered indexes for user tables should not be performed. This decreases the overall execution time. NOINDEX does not affect system tables because integrity checks are always performed on system table indexes.
- NO_INFOMSGS – Suppresses all information messages.
- PHYSICAL_ONLY – Limits the checking to the integrity of the physical structure of the page and record headers and the allocation consistency of the database. This check is designed to provide a small overhead check of the physical consistency of the database, but it can also detect torn pages, checksum failures, and common hardware failures that can compromise a user’s data.
- TABLOCK – Causes DBCC CHECKDB to obtain locks instead of using an internal database snapshot. This includes a short-term exclusive (X) lock on the database. TABLOCK will cause DBCC CHECKDB to run faster on a database under heavy load, but decreases the concurrency available on the database while DBCC CHECKDB is running.
- DATA_PURITY – Causes DBCC CHECKDB to check the database for column values that are not valid or out-of-range. For example, DBCC CHECKDB detects columns with date and time values that are larger than or less than the acceptable range for the datetime data type; or decimal or approximate-numeric data type columns with scale or precision values that are not valid.
We’ll go over some of the REPAIR options in a different section below.
How often should I check for SQL Server corruption?
Every minute of every day. Just kidding.
If you have a daily maintenance window, it would be nice to check for data corruption daily. The faster you can catch it, the less harm it may do. I’ve noticed a lot of people run this on the weekend especially with larger databases. There’s no right or wrong with this, just make sure you have it scheduled periodically.
Do I have to run DBCC CHECKDB in my Production environment?
No, well Yes. Sort of.
To check for data corruption it does no good to run DBCC CHECKDB on your test environment..UNLESS you restore a copy of your production environment to test and then run it. BRILLIANT!
You may be questioning if some HA options are suitable such as AlwaysOn, Log Shipping, etc.? No, you need to check your production *live* environment.
Now that DBCC CHECKDB is configured and running, what am I looking for?
Congratulations! You have DBCC CHECKDB automated and running, but now what? If you have a SQL Server Agent Job setup then make sure you setup Database Mail, an operator, and a notification on the job. If the job succeeds then carry on with your beautiful day. If the job fails, then we have some work to do.
You may see an error like this:
The In-row data USED page count for object "tablename", index ID 2, partition ID 608313809829888, alloc unit ID 608313809829888 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE. [SQLSTATE 42000] (Error 2508) The In-row data RSVD page count for object "tablename", index ID 2, par... The step failed.or this:
Object ID 2088535921, index ID 0, partition ID 72345201021503994, alloc unit ID 72345201051571606 (type In-row data): Page (1:94299) could not be processed. See other errors for details. Msg 8939, Level 16, State 98, Line 1 Table error: Object ID 2088535921, index ID 0, partition ID 72345201021503994, alloc unit ID 72345201051571606 (type In-row data), page (1:94299). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. CHECKDB found 0 allocation errors and 2 consistency errors in table 'tablename' (object ID 2088535921). CHECKDB found 0 allocation errors and 2 consistency errors in database 'tablename'. repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Database).First, don’t panic. Second, check backups. DBCC CHECKDB errors usually tell you what needs to be done.
For the first error a DBCC UPDATEUSAGE will correct the page and row count inaccuracies in the catalog views. Pretty harmless.
The second error reports data corruption. The error mentions using repair_allow_data_loss as the minimum repair level. This means you can run the statement with this argument, but you may lose data. This is why I always recommend restoring to a backup if you can. You need to make sure the backup doesn’t contain corrupted data and you want to make sure there is no data loss.
How to repair a SQL Server database
If you don’t have a backup, we may need to use DBCC CHECKDB with a repair option. Here’s are the repair options that are available to use. These may or may not work and need to be used as a last resort:
- REPAIR_ALLOW_DATA_LOSS – Tries to repair all reported errors. These repairs can cause some data loss.
- REPAIR_REBUILD – Performs repairs that have no possibility of data loss. This can include quick repairs, such as repairing missing rows in non-clustered indexes, and more time-consuming repairs, such as rebuilding an index.
Like I said above, it’s very important to have up to date backups to recover from corruption. Corruption doesn’t care how much data you have, what version of SQL you are running, or how fancy your datacenter is.
I’m feeling pretty good about this, but what else can I use to protect my SQL instances?
DBCC CHECKDB should be ran on every SQL instance you have, but there are a couple of other ways that may help detect/prevent data corruption.
- SQL Server Agent Alerts – Brian Kelley wrote a nice tip on this topic here.
- Page verification – Make sure your databases are using CHECKSUM page verification. If you’re still running SQL 2005 or below this isn’t available, but after you upgrade make sure you change this setting. To see page verification settings use the following statement:
select name, page_verify_option_desc from sys.databasesNext Steps
- Make sure you check out the MSDN Library for more in depth details regarding DBCC CHECKDB
- MSSQLTips.com has a nice collection of tips on Database Consistency Checks that can may be helpful as well.
