Tuesday, February 9, 2010

Rebuilding msdb database or msdb corrupt

If your msdb got corrupted or goes in suspect mode, then you left with two options:
  1. Restore from last known good backup.
  2. Recreate it.
Most of you know how to restore it, in this post I will tell you how to recreate it.
Steps to recreate msdb:
  1. Detach the damaged msdb: You can't just detach msdb because you're not allowed to detach system databases. However, you can if you start the server with trace flag 3608. You can do this by shutting down the server, open cmd (command prompt) navigating to the directory 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn' (this is the location where you installed your SQL Server, it can be different on your server) and doing the following: sqlservr.exe -c -T3608
    In another command window run sqlcmd -AthenSp_detach_db 'msdb'; GO
  2. Remove the data and log files for msdb
  3. Run the instmsdb.sql script in the 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install' directory
  4. Now stop the cmd shell by ctrl + c then shutdown the server and start it again in normal way.
  5. And now create the control_client job.

Simple .

Please send me response if you face any issue.

No comments:

Post a Comment