sqlskills-logo-2015-white.png

CHECKDB From Every Angle: Tips and tricks for interpreting CHECKDB output


Last week at SQL Connections someone said that CHECKDB’s output is ‘useless’.  Given that I wrote CHECKDB for SQL Server 2005 I was only mildly offended :-) But there’s a lot of truth in that statement – the error messages from CHECKDB do tell you exactly what’s corrupt in the database but they’re not exactly easy to read and understand unless you’re intimately familiar with the on-disk structures used to store a SQL Server database. It’s pretty easy to understand the output if there’s only one or two error messages, but once you get past 10 or so it becomes extremely difficult to figure out what the actual problem is for normal people (i.e. not CHECKDB geeks like me, Ryan or a handful of senior people in Product Support)


So, how are you supposed to figure out what’s wrong? In this post I want to run through a few tips and tricks you can use.



  • If the recommended repair level (at the very bottom of the output) is REPAIR_REBUILD then only non-clustered indexes are damaged. This meanscyou don’t need to run repair (which needs the database in single-user mode) or restore from backups. You can just manually rebuild the damaged indexes to fix the problem, possibly even online if you’re running Enterprise Edition and the index doesn’t have any LOB columns.
  • There are a couple of cases where even if it’s only non-clustered indexes that are damaged, REPAIR_ALLOW_DATA_LOSS may still be the recommended repair option. In this case, check the index ID in all the error messages – if all index IDs are 2 or higher, then it’s only non-clustered indexes that are damaged and you can rebuild them yourself instead of running repair/restore.
  • Figure out a list of tables that are affected by looking at the summary messages about how many corruptions were found per table. If each of the tables is isolated on a separate filegroup then these filegroups can be taken offline and restored individually, possibly without interrupting the application workload. If there’s no backup, then you can run DBCC CHECKTABLE with repair, for a faster repair operation than running a full CHECKDB.
  • Figure out the list of damaged pages. There are a number of error messages that could point to a damaged page – usually 8928 or 2537 – and then you can choose to do single-page restores from your backups to minimize downtime, possibly even online in Enterprise Edition.
  • Are there any errors that CHECKDB can’t repair?  If so, you have no choice but to restore from backups or to extract as much info as possible into a new database. Things to look for that mean repair won’t be able to fix everything are:

    • CHECKDB stops early and complains about system table pre-checks failing (errors 7984 – 7988 inclusive)
    • CHECKDB reports any metadata corruption (8992, 8995 errors)
    • CHECKDB reports any errors on PFS page headers (8939, 8946 errors with a possible 8998 error as well)

While I was at Microsoft I wrote two 200-page+ documents detailing all the CHECKDB error messages for SQL Server 2000 and 2005 – the team says they should all be translated into Books Online entries by next summer – a bunch of them are there already. In the meantime, I hope the hints above will take a little of the mystery out of CHECKDB’s output.

12 thoughts on “CHECKDB From Every Angle: Tips and tricks for interpreting CHECKDB output”

  1. Paul,

    If you dump the output of dbcc checkdb REPAIR_REBUILD to a table using the WITH TABLERESULTS option, how to you analyze the data (via query) to verify all the corruption was able to be repaired? I need to do this as part of an automated job. Any help would be greatly appreciated.

    Reply

    1. You need to look for the found count = fixed count. And then you need to run repair again to make sure it doesn’t find anything else (from a repair opening up a bit more of the database to being checked).

      Reply

      1. As an accidental DBA, your blog is an essential lifeline for me (along with the SQL Internals and the handouts from the immersion days)
        Currently DBCC docs are here: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql
        Sadly the online docs are missing out on your great insights (i.e. explaining error messages and down-to-earth solutions to real problems)
        It is great to see that all your work made it through to the community, I truly love it :)
        Ciao Paul, see you in Dublin

        Reply

  3. Where can I find details about the information returned by CHECKDB, especially when using WITH TABLERESULTS?
    I’m designing a job to run CHECKDB on all of our servers, as nothing has ever been run on them in who knows how many years – I’m the new DBA, if you hadn’t guessed – and I want to save the results to a permanent table for later analysis.

    Reply

    1. There isn’t a good, published description of all the messages. If you Google for CHECKDB TABLERESULTS you’ll find people who have published scripts to capture the output that you want.

      Reply

      1. Yes, I found loads of those, though most create a table with RepairLevel being an INT. I’m running SQL Server 2016 at work, and 2017 on my laptop for testing. On 2017, I find RepairLevel is a VARCHAR not an INT. Do you know if that is the same on 2016?
        So far haven’t been able to find anything that provides a description of what each column means. Some are obvious, but others aren’t.

        Reply

        1. It’s undocumented and can change from version to version, so there’s no official description. They’re all to do with error messages, and you can work them out for each error message as you also get the entire message as part of the result set.

          To be honest, most of the time you just need to know was there any output, then you’ve got problems. Always use WITH NO_INFOMSGS too.

          Reply

          1. BTW Thanks for your awesome insight into CHECKDB, I’ve read many of your blogs and am also using your example corrupt DBs for testing.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.

Collaborate With Us
Choose a Course