Corrupt master and msdb databases

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

  • 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" 😉

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

  • 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" 😉

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

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

  • Now a Days, when you search on the Google, many companies providing a data recovery services as well as recovery software. In such types of software one is recoveryfix for sql repair software, this company offering a free trial version software, with the help of this trial version you can see the preview of recoverable data.

    Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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