dbcc checkdb failed for master database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

dbcc checkdb failed for master database

Author

Topic

zaty2405
Yak Posting Veteran

58 Posts

Posted - 2010-01-28 : 13:00:16

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

22859 Posts

Posted - 2010-01-28 : 13:08:26

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 ...

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-01-28 : 13:20:03

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

zaty2405
Yak Posting Veteran

58 Posts

Posted - 2010-01-28 : 13:29:10

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 ).

zaty2405
Yak Posting Veteran

58 Posts

Posted - 2010-01-28 : 14:39:18

Hi,

Is there anything that I can do to resolve this ?

Is restoring master database from backup is the only solution?

Thanks

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-01-28 : 15:05:33

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

zaty2405
Yak Posting Veteran

58 Posts

Posted - 2010-01-28 : 15:58:10

Thanks.

How do I check what caused this? I could not find any io error or disk error ....

Kristen
Test

22859 Posts

Posted - 2010-01-29 : 02:29:58

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!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-01-29 : 07:47:35

Also check and ensure that firmware for raid/san is up to date.

--
Gail Shaw
SQL Server MVP

zaty2405
Yak Posting Veteran

58 Posts

Posted - 2010-02-02 : 11:56:51

Thanks Gail & Kristen for your help. I will be restoring the master database this weekend..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources