It has been a long time since I wrote a post. I had so so much work to do, so that I had no time for posting 🙁
Anyway I want to change that in the future. I don’t promise that I will write every single week but I will do my best.
So about the bug. Last week, my colleague reported me that our consistency checking job was failed. More interestingly it failed on the master. Even more interestingly he said, when he tried to run the check manually with
|
1 |
DBCC CHECKDB (master) WITH NO_INFOMSGS, ALL_ERRORMSGS |
the check run without any error. The error come up only when he used DATA_PURITY option:
|
1 2 3 4 5 6 |
dbcc checkdb(master) with all_errormsgs, no_infomsgs, data_purity Msg 2570, Level 16, State 2, Line 1 Page (1:354), slot 27 in object ID 58, index ID 1, partition ID 281474980511744, alloc unit ID 281474980511744 (type "In-row data"). Column "name" value is out of range for data type "nvarchar". Update column to a legal value. CHECKDB found 0 allocation errors and 1 consistency errors in table 'sys.sysbinobjs' (object ID 58). CHECKDB found 0 allocation errors and 1 consistency errors in database 'master'. |
This is what attracted my attention. I checked BOL which sait: “For databases created in SQL Server 2005 and later, column-value integrity checks are enabled by default and do not require the DATA_PURITY option. For databases upgraded from earlier versions of SQL Server, column-value checks are not enabled by default until DBCC CHECKDB WITH DATA_PURITY has been run error free on the database. After this, DBCC CHECKDB checks column-value integrity by default. For more information about how CHECKDB might be affected by upgrading database from earlier versions of SQL Server, see the Remarks section later in this topic.”
Our affected instance is a ‘native’ SQL Server 2008 and I double-checked that so as the result of the DBCC CHECKDB command. Ok, from that time it was obvious to me that something is wrong. Maybe the way DBCC CHECKDB is working or the description of DATA_PURITY option in BOL or something else. Since we were dealing with system table inconsistency in the master database I contacted Paul Randal who is the expert of this field (too). Paul confirmed that there is bug here. See more about this bug on his blog…