Corrupt table in master

Question

Post reply

Corrupt table in master

DanGalf

SSC-Addicted

Points: 429
More actions
October 26, 2012 at 8:35 am

#264648

I just discovered an issue for a new customer. Thankfully it is in their TEST environment and they haven't been complaining about anything. I was trying to see the properties of their linked servers, and kept getting errors.
When I run
select *from master.sys.servers
It says:
Msg 605, Level 21, State 3, Line 1
Attempt to fetch logical page (1:90) in database 1 failed. It belongs to allocation unit 25896092997713920 not to 281474979528704.
When I run
DBCC CHECKDB('master') WITH NO_INFOMSGS, ALL_ERRORMSGS
It says:
Msg 605, Level 12, State 3, Line 1
Attempt to fetch logical page (1:1115) in database 32767 failed. It belongs to allocation unit 25896092997713920 not to 281474980642816.
I know I could run DBCC CHECKDB with ALLOW_DATA_LOSS. But I have been reading about this, and hoping that there maybe is a workaround. Also, I am not sure how to proceed getting master into single user mode to run the repair. And I'm not sure how to tell the customer what to expect for downtime or data loss.
Any help would be appreciated.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Do you have a good backup you could restore from? That's the preferred solution.

John

DanGalf SSC-Addicted Points: 429 More actions · Answer 2

I'd have to ask them if an old backup is available to retrieve the sys.servers data, or any other corrupt pages. I've recently been brought it to do a specific task, not to administer their server yet, so I am not familiar with the backups. I will ask, but if they don't have it, or if that backup has corrupt data, I will need to come up with a plan.

DanGalf SSC-Addicted Points: 429 More actions · Answer 3

It doesn't look like they have a backup. They haven't had a DBA, so they are not following best practices in a lot of areas.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Please run the following and post the full, complete and unedited output.

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

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

OK, start by making a backup now. It's fairly easy to get your server into single user mode - there's plenty of information out there on how to do it. Needless to say, your users will be twiddling their thumbs while you do this. I don't know how long it'll take to do the data repairs, I'm afraid.

John

Lowell SSC Guru Points: 323518 More actions · Answer 6

depending on the results of this command, we might be able to tell you if you can do anything; whether it's a repair or a re-install/rebuild of master:

post teh entire results of this command:

DBCC CHECKDB('db name') WITH NO_INFOMSGS, ALL_ERRORMSGS

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

DanGalf SSC-Addicted Points: 429 More actions · Answer 7

The result of

DBCC CHECKDB ('master') WITH NO_INFOMSGS, ALL_ERRORMSGS

is:

Msg 605, Level 12, State 3, Line 1

Attempt to fetch logical page (1:1115) in database 32767 failed. It belongs to allocation unit 25896092997713920 not to 281474980642816.

I don't understand "database 32767" because that is not in sys.databases.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Please post the complete and unedited output of the command I gave you. Not just the one error. Every single thing that checkDB returned.

Do not repair, take the DB down, or do anything else at this point.

Database 32767 is the system resource database.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

DanGalf SSC-Addicted Points: 429 More actions · Answer 9

That is really all that it returns when I run the command DBCC CHECKDB ('master') WITH NO_INFOMSGS, ALL_ERRORMSGS. I hope I am doing it correctly.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

So there's no 'minimum level to repair' line? No 'x allocation errors and x consistency errors' line?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

DanGalf SSC-Addicted Points: 429 More actions · Answer 11

No, nothing like that. The two lines are all that are printed to the screen.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Take backups of master, model and msdb

Rebuild the system databases (documented in Books Online)

Restore the backups that you took (master, model and msdb)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

DanGalf SSC-Addicted Points: 429 More actions · Answer 13

Thanks so much for your help. I'll take that approach.

DanGalf SSC-Addicted Points: 429 More actions · Answer 14

The company noticed some system updates needed to be installed. So they did that, and restarted the server. And now the CHECKDB error is gone, and I'm able to access the linked servers. So it was a relief to not have to rebuild the system databases. But not a good feeling to not understand what happened.