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.
As usual, suggestions and comments are welcome.