October 21, 2014 at 2:30 am
Hi guys
sorry for my bad English. But I did lots of searchs and cannot find a way to fix this error. So just wondering whether I can find some helps here.
Basically we just built a brand new SQL Server 2012 box. However when it runs backup maintenance plan we found consistency error.
when run the command
dbcc checkdb ('master') with no_infomsgs
and below is the error messages I got.
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).
I followed MSDN and rebuilt the system databases. But after restart the SQL services, the error is still there.
Does anyone know how to get it fixed?? Thanks in advance.
October 21, 2014 at 10:39 am
Do you have anything else on this box?
I'd check the storage sub-system as it sounds like you have a bad disk and rebuilding the system databases put them back on the bad disk. Do you have another disk you can move the system databases to and then rebuild them?
I'm going to try to get some people much better with corruption issues than I am to take a look at your question.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 21, 2014 at 12:59 pm
Can you please post exactly what you did to rebuild the master database?
Also, do you by chance have another instance on the server? Please confirm that the appropriate server has been connected to when performing the checkdb as well as the master database rebuild.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 21, 2014 at 3:19 pm
Jack Corbett (10/21/2014)
Do you have anything else on this box?I'd check the storage sub-system as it sounds like you have a bad disk and rebuilding the system databases put them back on the bad disk. Do you have another disk you can move the system databases to and then rebuild them?
I'm going to try to get some people much better with corruption issues than I am to take a look at your question.
Thanks for reply.
While I did the search I saw lots of suggestions about checking I/O or memory. However the server is on a visual environment, I/O or memory issue will lead to all other visual boxes I believe. So I think the chance of IO/Meomory error wont be too high.
But we will schedule a memory diagnostic and disk defragment on the weekend.
Thanks again.
October 21, 2014 at 3:26 pm
SQLRNNR (10/21/2014)
Can you please post exactly what you did to rebuild the master database?Also, do you by chance have another instance on the server? Please confirm that the appropriate server has been connected to when performing the checkdb as well as the master database rebuild.
Thanks for reply.
What I did is following MSDN suggestion to rebuild the sysyem database:
1. backup all system databases
2. shut down sql service
3. rename system database mdf file to *_bk (not necessary I believe, but just did it in case the rebuild cannot over-write the file)
4. use command line console to rebuild the system database
5. review rebuild summary report, and confirmed there is no error report under error section
6. confirmed datestamp of the master.mdf file under data folder has been changed
but when service is back online, I did a dbcc checkdb again against master database, the consistency error is still there.
For your 2nd question, the server is a single instance server so there is no other instance installed on this server. (Just out of the topic, even it is a multi-instance server, DBCC CheckDB should not post any error, is this correct?)
October 21, 2014 at 3:33 pm
qiyongzhi (10/21/2014)
SQLRNNR (10/21/2014)
Can you please post exactly what you did to rebuild the master database?Also, do you by chance have another instance on the server? Please confirm that the appropriate server has been connected to when performing the checkdb as well as the master database rebuild.
Thanks for reply.
What I did is following MSDN suggestion to rebuild the sysyem database:
1. backup all system databases
2. shut down sql service
3. rename system database mdf file to *_bk (not necessary I believe, but just did it in case the rebuild cannot over-write the file)
4. use command line console to rebuild the system database
5. review rebuild summary report, and confirmed there is no error report under error section
6. confirmed datestamp of the master.mdf file under data folder has been changed
but when service is back online, I did a dbcc checkdb again against master database, the consistency error is still there.
For your 2nd question, the server is a single instance server so there is no other instance installed on this server. (Just out of the topic, even it is a multi-instance server, DBCC CheckDB should not post any error, is this correct?)
I ask about an additional instance because I have seen people connect to the wrong instance many times on the server and make the mistake of thinking they were in the correct instance.
But since a fresh install of master / rebuild of master did not fix the corruption, it sounds like you have an IO problem. Have you checked the disk via checkdsk or scandisk? Check the IO paths to ensure there are no faults. Check the storage logs for any errors.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 21, 2014 at 3:53 pm
Thanks for sharing your thought, SQLRNNR.
While we will do a IO/memory check on the weekend, I cannot do too many things at the moment I believe.
Through the research the affected object is not a fatal error so at the moment all services and client databases are running smoothly.
But since it is a abrand new instance, we prefer to set it up with 0 error.
I may need to try some other methods to narrow down the error causes.
October 21, 2014 at 5:45 pm
Which parameter is used to rebuild the database, 'REPAIR_ALLOW_DATA_LOSS' or REPAIR_REBUILD? If you use REPAIR_ALLOW_DATA_LOSS, then you must backup your database first since this parameter will cause data loss.
If REPAIR_ALLOW_DATA_LOSS still does not work, then you may need to use third-party tools such as DataNumen SQL Recovery, which works well for my cases.
October 21, 2014 at 8:12 pm
keithy_sunny (10/21/2014)
Which parameter is used to rebuild the database, 'REPAIR_ALLOW_DATA_LOSS' or REPAIR_REBUILD? If you use REPAIR_ALLOW_DATA_LOSS, then you must backup your database first since this parameter will cause data loss.If REPAIR_ALLOW_DATA_LOSS still does not work, then you may need to use third-party tools such as DataNumen SQL Recovery, which works well for my cases.
Hi Keithy_sunny
Thanks for suggestions, but unfortunately the error occurs at Master database, DBCC repair command needs to set the database to single user mode, but system database is not allowed to set to single user mode.
I am looking at 3rd party tools as you suggested. Thanks a lot.
October 22, 2014 at 3:03 am
Hi Keithy_sunny
Sorry I was wrong. I signed on via single user mode and fixed the master DB successfully.
However the error is interesting. I am dowing more tests with collegues at the moment. Will post my findings to close this thread.
October 30, 2014 at 1:34 am
Just to close this thread:
The DBCC error is not because of corruption of database. It seems the error leads to the installation media, the 2012 CD released in 2012
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 return nothing.
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
Because the error reports on nsobj, I guess it may be because of hotfix conflict between windows server and sql server.
Interesting, isnt it?:-)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply