SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 dbcc checkdb failed for master database
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

zaty2405
Yak Posting Veteran

58 Posts

Posted - 01/28/2010 :  13:00:16  Show Profile  Reply with Quote
Hi,

dbcc checkdb failed on master database on one of my production server


Could not find the index entry for RID ''1658e0ef6a04000000030000'' in index page (1:434), index ID 0, database ''master''.

I execute DBCC CHECKTABLE (syscolumns) WITH NO_INFOMSGS, ALL_ERRORMSGS

Here is the output :

Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'master', index 'syscolumns.ncsyscolumns' (ID 3) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:425:11) with values (id = 1794105432 and name = 'COLUMN_NAME' and number = 0 and id = 1794105432 and colid = 4 and number = 0)
points to the data row identified by ().
CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'syscolumns' (object ID 3).
repair_fast is the minimum repair level for the errors found by DBCC CHECKTABLE (master.dbo.syscolumns ).

Since this a master database, I am not sure on what action to take next

Thank you

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/28/2010 :  13:08:26  Show Profile  Reply with Quote
My thoughts would be:

Likely cause is Disk I/O fault. Find cause of the disk corruption and repair - otherwise the damage will spread.

I would want to have a copy of MASTER in case anything went wrong during repair so I could "have another go". Stop SQL and copy the MASTER.MDF and MASTER.LDF somewhere safe?

Have you got a recent backup of MASTER? If so restore that would be my first option. Will anything have changed since the backup? (I reckon that is the tricky issue with Master)

Appears to be in an Index. No idea if it is possible to easily Drop and Recreate the index, in Master, but if that works it might fix it.

Maybe repair-fast will fix it without further problems, or any data loss. I presume this is a SQL2000 database? (REPAIR_FAST is deprecated form SQL 2005 I think, so presumably would not have been suggested!).

Repair will have to be in single user, so I think you will have to start SQL in emergency mode.

But I'm no expert, so proceed with caution ...
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/28/2010 :  13:20:03  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Run this and post all the results. There may be more than just syscolumns damaged.


DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS


Do you have a clean backup of master?

--
Gail Shaw
SQL Server MVP

Edited by - GilaMonster on 01/28/2010 13:20:28
Go to Top of Page

zaty2405
Yak Posting Veteran

58 Posts

Posted - 01/28/2010 :  13:29:10  Show Profile  Reply with Quote
Hi,

Here is the out put from
DBCC CHECKDB (master) WITH NO_INFOMSGS, ALL_ERRORMSGS



Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'master', index 'syscolumns.ncsyscolumns' (ID 3) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:425:11) with values (id = 1794105432 and name = 'COLUMN_NAME' and number = 0 and id = 1794105432 and colid = 4 and number = 0) points to the data row identified by ().
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 12, index ID 0: Page (1:480) could not be processed. See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 12, index ID 0, page (1:480), row 164. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 85 and 24.
Server: Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 12, index ID 1. Page (1:480) was not seen in the scan although its parent (1:284) and previous (1:800) refer to it. Check any previous errors.
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 12, index ID 1. Page (1:807) is missing a reference from previous page (1:480). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'syscolumns' (object ID 3).
CHECKDB found 0 allocation errors and 4 consistency errors in table 'sysdepends' (object ID 12).
CHECKDB found 0 allocation errors and 5 consistency errors in database 'master'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (master ).
Go to Top of Page

zaty2405
Yak Posting Veteran

58 Posts

Posted - 01/28/2010 :  14:39:18  Show Profile  Reply with Quote
Hi,

Is there anything that I can do to resolve this ?

Is restoring master database from backup is the only solution?

Thanks
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/28/2010 :  15:05:33  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Yup. You should restore from backup. The additional errors change this from a minor error to a nasty one that cannot be repaired without data loss, and I would never recommend that on system tables in a system database. Too much risk.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

zaty2405
Yak Posting Veteran

58 Posts

Posted - 01/28/2010 :  15:58:10  Show Profile  Reply with Quote
Thanks.

How do I check what caused this? I could not find any io error or disk error ....
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/29/2010 :  02:29:58  Show Profile  Reply with Quote
Disk errors usually recorded in Event Viewer.

There might be some sort of surface scan tool for your disk controller / disks?

For many years I have been aware of a product called "Gibson spinrite". Never used it, but the people are still in business, so maybe it is a worthwhile tool. From memory I think its www.grc.com - Google will be more reliable than my memory though!
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/29/2010 :  07:47:35  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Also check and ensure that firmware for raid/san is up to date.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

zaty2405
Yak Posting Veteran

58 Posts

Posted - 02/02/2010 :  11:56:51  Show Profile  Reply with Quote
Thanks Gail & Kristen for your help. I will be restoring the master database this weekend..
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000