Data base need integrity check

• 
  Hi,
  
  SQL Response checks the current SQL Server's error log and raises the recommendation for Integrity Check if it doesn't get any 'DBCC CHECKDB' statement in the current SQL Server's error log file in the last seven days.
  
  If the current SQL Server error log is less than seven days old then it doesn't raise any recommendation.
  
  Can you please let us know what exact sql statement are you running?
  
  We would really appreciate if you can also tell us your thoughts on this ... was this recommendation helpful, or would you expect something else and is the frequency alright etc etc and anything more!!
  
  Thanks.
  
  Regards,
  Priya

  priyasinha February 14, 2008 11:11

  - 0 +
• 
  I run Standard scheduled job (optimization and integrity) on Monday, Wednesday and Friday at 4.00 and 5.00 am.
  So, yesterday (Monday) the job runs fine.
  
  This one is for Integrity : 5.00 am.
  EXECUTE master.dbo.xp_sqlmaint N'-PlanID 546C75EF-FB62-493F-946E-8D62F8BC7A2C -Rpt "C:\Archivos de programa\Microsoft SQL Server\MSSQL\LOG\DB Maintenance Plan12.txt" -DelTxtRpt 2WEEKS -WriteHistory -CkDBRepair '
  
  This one is for Optimization: 4.00 am.
  
  EXECUTE master.dbo.xp_sqlmaint N'-PlanID 546C75EF-FB62-493F-946E-8D62F8BC7A2C -Rpt "C:\Archivos de programa\Microsoft SQL Server\MSSQL\LOG\DB Maintenance Plan10.txt" -DelTxtRpt 2WEEKS -WriteHistory -RebldIdx 10 '
  
  My suggestion is: If the job fail or the job does not running, said for 2 or 3 days, then the Response Recommendation is fine. Otherwise, to me, is a false alarm.

  Luis Martin February 14, 2008 13:07

  - 0 +
• 
  Could I ask what version of SQL Server you are using?
  
  SQL Response checks the log file looking for DBCC CHECKDB lines. There was an issue with SP2 of Sql Server 2005 that meant that when using maintainance plans it would lose the database context and run the check on the default database. If this happens the log shows
  
  DBCC CHECKDB (mssqlsystemresource) WITH no_infomsgs ((etc.))
  
  rather the name of the DB it was meant to check. The information can be found here http://support.microsoft.com/kb/934459/ which as I read it meant that the maintainance plans weren't actually checking the DB.
  
  I tried this locally on a test box and adding the fix from that link fixed it here.
  
  Reading this http://blogs.msdn.com/psssql/archive/20 ... ained.aspx it looks like there were multiple issues with SP2 that needed patching (indeed they re-released SP2 after a while it seems)

  Nigel Morse February 15, 2008 08:28

  - 0 +
• 
  Hi,
  
  I'm using 2000 SP4 fix 2040.

  Luis Martin February 15, 2008 12:44

  - 0 +
• 
  Well that blows that theory out the water... (although was good to know anyway for when someone else has the issue!)
  
  We'll take a look at the same version you're using and get back to you.
  
  Thanks

  Nigel Morse February 18, 2008 06:53

  - 0 +
• 
  Hi Luis,
  
  We are able to reproduce this bug here but we need little more information. Can you please look at your current SQL Server log file(C:\Program Files\Microsoft SQL Server\MSSQL(instance)\LOG\ERRORLOG) just after your maintenance job has run and mail me or post the exact DBCC statement in your log file? This will help us to fix this issue.
  
  My email id is priya.sinha@red-gate.com.
  
  Thanks a lot.
  
  Regards,
  Priya

  priyasinha February 18, 2008 08:13

  - 0 +
• 
  Just to add to what Priya said - when it does a check DB SQL Server should write to the error log with a line similar to...
  

  DBCC CHECKDB ( database name )
  

  
  or
  

  DBCC CHECKALLOC
  

  
  or something like that. You could either email us the whole log file, or if you're not comfortable with that then just an example of the line in question.

  Nigel Morse February 18, 2008 08:18

  - 0 +
• 
  We think we've found the problem causing this and hopefully have fixed it for next release.

  Nigel Morse February 19, 2008 03:43

  - 0 +

Add comment

Please sign in to leave a comment.