Help!!!! Corrupt MSDB

• csoloway

  Ten Centuries

  Points: 1027

  More actions

  March 16, 2007 at 1:27 pm

  #117908

  I recently took over support of a cluster that it appears a couple of tables in MSDB are corrupt.  Doesn't do me any good to restore from backup because it appears this has been going on for a while.  The main table that's corrupt is backupset.

  How do I rebuild this table?  Would this work?

  Kill off all the users:

   

  use master

  go

  alter database msdb set single_user

  go

   

  user msdb

  go

  dbcc checkdb ('msdb', repair_allow_data_loss)

  go

   

  or is there a better way.

• MohammedU

  SSCoach

  Points: 19644

  More actions

  March 16, 2007 at 6:20 pm

  #695565

  You can use  DBCC CHECKTABLE also in place of CHECKDB....

  You can also restore the backup of current msdb on to deve server and repair it then take the backup and restore it to production...

   

   

  MohammedU
  Microsoft SQL Server MVP

• Amit Jethva

  SSCarpal Tunnel

  Points: 4707

  More actions

  March 19, 2007 at 1:27 am

  #695638

  -> MSDB is used by SQLAgent. so you need to stop SQLAgent first. Then run your alter database commands.

  any repair options with checkdb require you to have db in single user mode.

  
  

  -- Amit

  ---

  
  "There is no 'patch' for stupidity."

  ---

  
  Download the Updated SQL Server 2005 Books Online.

• tobias

  SSC Enthusiast

  Points: 130

  More actions

  March 19, 2007 at 9:41 am

  #695753

  You can start SQL Server up in Single User mode, bypass the MSDB, detach the MSDB, attach just about any old MSDB (say from a virgin install), shut down SQL, and restart normally.

  THis will give you a working SQL Server, albeit w/o the agent information. You can then use the usual tools to restore the MSDB from back-up.