Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server DBCC CHECKDB Overview


By:   |   Read Comments (2)   |   Related Tips: More > Database Consistency Checks DBCCs


Free MSSQLTips Webcast Today >> Optimize SQL Server Performance


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 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.databases
      
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Brady Upton Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, July 26, 2016 - 12:27:56 PM - Ramesh K Back To Top

Based on your experience, how long the DBCC command would take for a 50 Gig database ? The time it takes to run is it predictable ? given CPUs and Mem, SAN  etc..  Can you run this command incrementally or when ever you run it, it has to go thru the entire file.


Tuesday, July 26, 2016 - 10:24:55 AM - Joe Back To Top

Nice article.  I use the same methods in my daily maintenance plans.  What is the best way to recover data from a good backup to the corrupted DB (that would now be repaied), if you used the Repair_Allow_data_loss option?  Is that even possible?  I'd think the ID's from those records would be orphaned/mismatched.  The thought of restoring a database from the day before means a whole days worth of data lost.

 


Learn more about SQL Server tools