July 14, 2009 at 10:00 am
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 ).
July 14, 2009 at 10:17 am
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
July 14, 2009 at 11:36 am
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?
July 14, 2009 at 11:57 am
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
July 14, 2009 at 12:21 pm
So based on that, do you still think my best bet is to restore from the old backup after I script out my jobs?
July 14, 2009 at 12:34 pm
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
July 14, 2009 at 1:20 pm
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.
July 14, 2009 at 1:37 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 14, 2009 at 1:49 pm
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply