How to get all the corruption messages from CHECKDB

Hopefully all of you reading my blog already know to use the WITH ALL_ERRORMSGS option whenever you run DBCC CHECKDB (which is now the default in 2005 SP3, haven't checked if it made it into 2008 SP1). If you're doing some quick analysis of the results to see, for example, whether it's just nonclustered indexes that are corrupt then you need to see all the errors – as it may look like all the errors are (relatively) benign but there's one error you didn't see that says a clustered index data page is corrupt.

That option is all well and good *except* when you run the DBCC command through SSMS. It will only show the first 1000 errors (long story, but let's just says it involves me being able to create corruption cases during 2005 development that would generate so many errors it would cause SSMS to crash) and there's no way to get around this. So, if you have more than 1000 errors, as in a case I'm helping with today where there are 19000+ errors, you can't use SSMS and expect to get them all back. Problem is, you don't know that you've got that many errors until you run it – so if you used SSMS and find there are more than 1000 errors, AND you want to do some analysis of them, you'll need to go and run it again using a different connection to the server.

So – something I advise is to run DBCC from the command line. Either osql or sqlcmd will do the trick. For instance:

osql -E -Q"DBCC CHECKDB (master) WITH ALL_ERRORMSGS, NO_INFOMSGS" -oC:\outputfile.txt

This will run the DBCC command and store the output in a file for later analysis. You can substitute sqlcmd for osql in the command above if you want.

6 thoughts on “How to get all the corruption messages from CHECKDB

  1. I don’t have one handy otherwise I’d test it myself so… Does this work on SQL 2000 as well? I have some support issues that could be helped greatly by this.

  2. Hi Paul,
    I know the post is old but I ran across it while looking for a different DBCC issue.

    I have been using this query to capture the results. I assume this approach gets around the SSMS limitation?

    Create Table #DBCC_ChkTab (….)
    Go
    Declare @cmd NVarchar(max);
    Set @cmd = N’DBCC CheckTable(”dbo.MyTable”) With TableResults, All_ErrorMsgs, No_InfoMsgs;’
    Truncate Table #DBCC_ChkTab;
    Insert Into #DBCC_ChkTab Exec sp_ExecuteSQL @cmd;

    Select * From #DBCC_ChkTab;

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.