DBCC CheckDB('Master') Error - Rational Diagnostic

This is a error when I deploy was working on SQL Server migration project.

Actually I would like to log the whole migration project, start from the planning, to end of migration, with the exception handlings. Reason for that is because, if you search online, you will find tons of articles talking about best practice for "upgrade", however you couldn't find too many resources about best practice of "migration". The SQL Server migration task, is to migrate databases from our current SQL Server 2008 R2 Standard Edition database server, to SQL Server 2012 Entreprise Edition on another VM server.

But today I would like to talk about the DBCC master error after I built the 2012 instance.

Long in short, I grabbed the installation disc, followed set up wizard, inputted administration account and service account, and assigned the file location. Everything looked good, and it went smooth.

After installation, I restarted the server, opened the SSMS, and as the most common task, did a DBCC check and then did the backup. Then I got below error message

 Msg 8928, Level 16, State 6, Line 1  
 Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown): Page (1:382) could not be processed. See other errors for details.  
 CHECKDB found 1 allocation errors and 0 consistency errors not associated with any single object.  
 Msg 8939, Level 16, State 98, Line 1  
 Table error: Object ID 44, index ID 1, partition ID 281474979594240, alloc unit ID 281474979594240 (type In-row data), page (1:382). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -1.  
 Msg 8906, Level 16, State 1, Line 1  
 Page (1:382) in database ID 1 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.  
 Msg 8906, Level 16, State 1, Line 1  
 Page (1:502) in database ID 1 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.  
 Msg 2575, Level 16, State 1, Line 1  
 The Index Allocation Map (IAM) page (1:382) is pointed to by the next pointer of IAM page (0:0) in object ID 44, index ID 1, partition ID 281474979594240, alloc unit ID 281474979594240 (type In-row data), but it was not detected in the scan.  
 Msg 8939, Level 16, State 98, Line 1  
 Table error: Object ID 44, index ID 1, partition ID 281474979594240, alloc unit ID 281474979594240 (type In-row data), page (1:382). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -1.  
 Msg 7965, Level 16, State 2, Line 1  
 Table error: Could not check object ID 44, index ID 1, partition ID 281474979594240, alloc unit ID 281474979594240 (type In-row data) due to invalid allocation (IAM) page(s).  
 CHECKDB found 4 allocation errors and 2 consistency errors in table 'sys.sysnsobjs' (object ID 44).  
 CHECKDB found 5 allocation errors and 2 consistency errors in database 'master'.  
 repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (master).  

OMG, if you do a quick google search, you will get tons of troubleshooting methods and suggestions. But probably 9 out of 10 will tell you, your disk has problem.

So let's think about this problem: They are on virtual environment, if one of them has disk fragment problem, eventually all virtual machines should get similar problems, so it should be the case.

So to test my assumption, I uninstalled SQL Server 2012 and then did a reinstall. I re-ran the DBCC command and get the identical error. So think about it, in a virtual environment, install a software  twice and system allocates the same sector to your contents, it doesnt look like a very high chance, isn't it?

So I started to look at this specific object, it is sysnsobjs from the Object_Name function. But when I did a google search, I couldn't find similar errors.

When I look at the MSDN I found the error content actually is related to some schema definition. So is it because of SP/schema change? as I know the installation disc was downloaded in 2012.

SO I downloaded the new installation disc, which was released in 2014 with SP2. Now time to test:

To repeat the error, I built a VM and have two CDs on hand:

• the old CD, 2012 released in 2012
• the new CD, 2012 released in 2014 with SP2

I installed two instances on the VM:

• DBCC CheckDB on the first instance returns identical error.
• DBCC CheckDB on the 2nd instance passed successfully

• Then I tried to apply SP2 on the first instance, error is still there
• Then I tried to use new CD to repair the first instance, error is still there
• Then I tried to repair master database via single user mode, the error is gone

 

So what is the conclusion? While from my experience, troubleshooting sometimes is fun, as you need to use your imagination. :D