dba_runCHECKDB v2(012)

Feb 27

Posted by


If you are one among the many that downloaded my consistency check stored procedure called “dba_RunCHECKDB”, you may have noticed a “small” glitch… it doesn’t work on SQL Server 2012!

This is due to the resultset definition of DBCC CHECKDB, which has changed again in SQL Server 2012. Trying to pipe the results of that command in the table definition for SQL Server 2008 produces a column mismatch and it obviously fails.

Fixing the code is very easy indeed, but I could never find the time to post the corrected version until today.

Also, I had to discover the new table definition for DBCC CHECKDB, and it was not just as easy as it used to be in SQL Server 2008. In fact, a couple of days ago I posted a way to discover the new resultset definition working around the cumbersome metadata discovery feature introduced in SQL Server 2012.

Basically, the new output of DBCC CHECKDB now includes 6 new columns:

    CREATE TABLE ##DBCC_OUTPUT(
        Error int NULL,
        [Level] int NULL,
        State int NULL,
        MessageText nvarchar(2048) NULL,
        RepairLevel nvarchar(22) NULL,
        Status int NULL,
        DbId int NULL, -- was smallint in SQL2005
        DbFragId int NULL,      -- new in SQL2012
        ObjectId int NULL,
        IndexId int NULL,
        PartitionId bigint NULL,
        AllocUnitId bigint NULL,
        RidDbId smallint NULL,  -- new in SQL2012
        RidPruId smallint NULL, -- new in SQL2012
        [File] smallint NULL,
        Page int NULL,
        Slot int NULL,
        RefDbId smallint NULL,  -- new in SQL2012
        RefPruId smallint NULL, -- new in SQL2012
        RefFile smallint NULL,  -- new in SQL2012
        RefPage int NULL,
        RefSlot int NULL,
        Allocation smallint NULL
    )

If you Google the name of one of these new columns, you will probably find a lot of blog posts (no official documentation, unfortunately) that describes the new output of DBCC CHECKDB, but none of them is strictly correct: all of them indicate the smallint columns as int.

Not a big deal, actually, but still incorrect.

I will refrain from posting the whole procedure here: I updated the code in the original post, that you can find clicking here. You can also download the code from the Code Repository.

As usual, suggestions and comments are welcome.

Posted on February 27, 2013, in SQL Server, T-SQL and tagged , , , , , . Bookmark the permalink. 3 Comments.

  2. Stephen Swan | May 1, 2013 at 7:32 pm

    This is a great script!

    A couple of things on the script could be changed though.
    1) The @AllMessages parameter should be @allMessages to be consistent and allow it to work on case-sensitive databases.
    2) Most people do not have SQL2000 servers anymore and could use sp_send_dbmail instead of cdosysmail. I.e.
    EXECUTE [msdb].[dbo].[sp_send_dbmail]
    @profile_name = @dbmail_profile,
    @recipients = @dbmail_recipient,
    @body = @body,
    @body_format = ‘HTML’,
    @subject = ‘Consistency error found!’
    3) I would add a drop statement to the script so that it could be easily updated using multi-server query tools.

    Thanks for putting all this together.

    Reply
  3. Binyam | November 15, 2013 at 3:20 pm

    I just found this today and I am loving it except for one thing; It would have been even nicer if there was a way to exclude databases. In its current for you have to do it all or do it one by one.

    Reply

Leave a comment