Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Corrupt master and msdb databases


Corrupt master and msdb databases

Author
Message
Andrew G
Andrew G
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2010 Visits: 2229
We've had corruption in master and msdb on a SQL2008R2 SP1 on Win2k8r2.

Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3851, State 1: An invalid row (class=12,depid=0,depsubid=0) was found in the system table sys.syssingleobjrefs (class=12).
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3851, State 1: An invalid row (class=13,depid=1,depsubid=0) was found in the system table sys.syssingleobjrefs (class=13).
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3851, State 1: An invalid row (class=60,depid=1,depsubid=0) was found in the system table sys.syssingleobjrefs (class=60).
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3851, State 1: An invalid row (class=26,depid=1,depsubid=0,indepid=3,indepsubid=0) was found in the system table sys.sysmultiobjrefs (class=26).
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3851, State 1: An invalid row (class=13,depid=2,depsubid=0) was found in the system table sys.syssingleobjrefs (class=13).
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3851, State 1: An invalid row (class=60,depid=2,depsubid=0) was found in the system table sys.syssingleobjrefs (class=60).
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3851, State 1: An invalid row (class=60,depid=3,depsubid=0) was found in the system table sys.syssingleobjrefs (class=60).
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3851, State 1: An invalid row (class=60,depid=4,depsubid=0) was found in the system table sys.syssingleobjrefs (class=60).
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3851, State 1: An invalid row (class=60,depid=5,depsubid=0) was found in the system table sys.syssingleobjrefs (class=60).
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3851, State 1: An invalid row (class=26,depid=259,depsubid=0,indepid=3,indepsubid=0) was found in the system table sys.sysmultiobjrefs (class=26).
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3851, State 1: An invalid row (class=26,depid=260,depsubid=0,indepid=3,indepsubid=0) was found in the system table sys.sysmultiobjrefs (class=26).
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3851, State 1: An invalid row (class=26,depid=261,depsubid=0,indepid=3,indepsubid=0) was found in the system table sys.sysmultiobjrefs (class=26).
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3851, State 1: An invalid row (class=26,depid=263,depsubid=0,indepid=3,indepsubid=0) was found in the system table sys.sysmultiobjrefs (class=26).
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3851, State 1: An invalid row (class=26,depid=272,depsubid=0,indepid=3,indepsubid=0) was found in the system table sys.sysmultiobjrefs (class=26).
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3851, State 1: An invalid row (class=26,depid=286,depsubid=0,indepid=3,indepsubid=0) was found in the system table sys.sysmultiobjrefs (class=26).
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3851, State 1: An invalid row (class=76,depid=65536,depsubid=100) was found in the system table sys.syssingleobjrefs (class=76).
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3851, State 1: An invalid row (class=76,depid=65536,depsubid=101) was found in the system table sys.syssingleobjrefs (class=76).
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3851, State 1: An invalid row (class=76,depid=65536,depsubid=102) was found in the system table sys.syssingleobjrefs (class=76).
CHECKDB found 0 allocation errors and 18 consistency errors not associated with any single object.


Error opening errorlog:
Exception happened when running extended stored procedure 'xp_readerrorlog' in the library 'xpstar.dll'. SQL Server is terminating process 254. Exception type: Win32 exception; Exception code: 0xc0000006.

I'm planning to do a restore of master & msdb in a few hours. I've rebuilt these databases before on a test system, but never restored. What is the recommended approach, rebuild, patch, then restore, or just restore?

Will also be patching the OS with http://support.microsoft.com/kb/2708811 NTFS.sys update as recommended by Microsoft support.
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8782 Visits: 16555
You may not need to rebuild, does the instance start in single user mode?
If so just proceed with the restores if the backups are recent.
If the instance does not start then yes, rebuild and patch then restore your master and msdb.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47218 Visits: 44372
If SQL starts, restore from lastest backups. If it doesn't start, rebuild the system databases and then once it does start restore the backups.


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


Andrew G
Andrew G
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2010 Visits: 2229
SQL service wouldn't restart.. so I ended up doing a repair option from install media. Funny enough, it repaired the problem with master and was then able to start, no restore or service pack required. I moved all the data onto a new drive, but still unsure how it all happened.
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8782 Visits: 16555
thanks for letting us know. Something i've done before is to extract the system db backups to a running instance as user databases, detach them and rename the files then plug them into the corrupt instance.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
db_expert_pradeep
db_expert_pradeep
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 523
It looks like consistency errors.If database is not start the rebuilt it and restore from latest backup.
Master will restore when SQL start in single user mode and when you going to restore msdb down the SQL Agent service.
Sinfonico25
Sinfonico25
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 167
SQL Server 2008 R2 has an Repair option in the installation wizard, it re-creates master and tempdb databases... i used it a few months ago and helped me a lot.

Hope it help you.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search