Is this an irrecoverable error that can only be resolved by restoring from a backup?

  • Sherri Barkley

    SSCrazy

    Points: 2269

    Due to some errors I was receiving in my backup jobs, I ran dbcc checkdb against all of the databases on my server and found the only one with any errors or inconsistencies is msdb. I am a rookie DBA and unfortunately do not have a senior member to get advice from (I am the only DBA). I inherited this system and noticed that the backups for the system databases on this server are old. So what I am trying to figure out is if I "repair_allow_data_loss", will I cause more issues? I know that if I restore from the only backups we have I will lose data because the backups are old, so data loss is inevitable in my case I am just trying to chose the lesser of two evils.

    Here is the output form dbcc checkdb with no_infomsgs, all_errormsgs:

    Server: Msg 8976, Level 16, State 1, Line 1

    Table error: Object ID 1090102924, index ID 1. Page (1:359) was not seen in the scan although its parent (1:4321) and previous (1:4404) refer to it. Check any previous errors.

    Server: Msg 8978, Level 16, State 1, Line 1

    Table error: Object ID 1090102924, index ID 1. Page (1:753) is missing a reference from previous page (1:359). Possible chain linkage problem.

    Server: Msg 8928, Level 16, State 1, Line 1

    Object ID 2069582411, index ID 2: Page (1:358) could not be processed. See other errors for details.

    Server: Msg 8976, Level 16, State 1, Line 1

    Table error: Object ID 2069582411, index ID 2. Page (1:358) was not seen in the scan although its parent (1:4138) and previous (1:4698) refer to it. Check any previous errors.

    Server: Msg 8978, Level 16, State 1, Line 1

    Table error: Object ID 2069582411, index ID 2. Page (1:4703) is missing a reference from previous page (1:358). Possible chain linkage problem.

    CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID 540225842)' (object ID 540225842).

    CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID 540357684)' (object ID 540357684).

    CHECKDB found 0 allocation errors and 3 consistency errors in table 'sysdbmaintplan_history' (object ID 1090102924).

    CHECKDB found 0 allocation errors and 3 consistency errors in table 'backupmediafamily' (object ID 2069582411).

    CHECKDB found 0 allocation errors and 8 consistency errors in database 'msdb'.

    repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (msdb ).

  • Gail Shaw

    SSC Guru

    Points: 1004484

    I personally would not recommend a repair on MSDB.

    http://www.sqlskills.com/BLOGS/PAUL/post/Is-running-repair-on-msdb-safe.aspx

    If you've got a clean backup, restore from it. Before you do so, script out all your jobs and alerts. The scripting should work, because the corruption doesn't appear to be in the job-related tables. That way you can ensure that all the jobs survive. Also save any SSIS packages that you have in MSDB to the file system, same with DTS packages.

    Can you identify which tables these are

    object_id 540225842

    and

    object_id 540357684

    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
  • Sherri Barkley

    SSCrazy

    Points: 2269

    Thanks Gail.

    When I run 'select * from sysobjects where id in (540225842, 540357684)' it returns no records. The same is true if I 'select object_name(540357684)' and 'select object_name(540225842)'.

    Is there some other way to find out what these objects are?

  • Gail Shaw

    SSC Guru

    Points: 1004484

    Sherri Barkley (7/14/2009)


    When I run 'select * from sysobjects where id in (540225842, 540357684)' it returns no records. The same is true if I 'select object_name(540357684)' and 'select object_name(540225842)'.

    In that case those aren't real object IDs and CheckDB's picking up odd objectids from the pages. It does sometimes happen.

    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
  • Sherri Barkley

    SSCrazy

    Points: 2269

    So based on that, do you still think my best bet is to restore from the old backup after I script out my jobs?

  • Gail Shaw

    SSC Guru

    Points: 1004484

    I thought that anyway.

    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
  • Sherri Barkley

    SSCrazy

    Points: 2269

    Thanks Gail. Based on the blog you provided the link to and your posts I assumed so, but since you asked the question about the two object id's I just wanted to verify that didn't change your opinion.

    Thanks again.

  • Jeffrey Williams

    SSC Guru

    Points: 88603

    I would check the object's for ID's: 1090102924, 2069582411.

    And then, I would follow Gail's advice and script out everything I could including verifying whether or not someone has user objects in that database.

    Once that is done, then I would make sure I modified all of my maintenance plans to include an integrity check on every database including system databases. If I could not get that scheduled on a daily basis, I would at least make sure it was done weekly.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Gail Shaw

    SSC Guru

    Points: 1004484

    And schedule regular backups of the system databases. Weekly's probably enough, unless you make lots of changes to the system databases.

    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

Viewing 9 posts - 1 through 9 (of 9 total)

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