Wednesday, September 12, 2012

Rebuild MSDB Database

If the transaction log file for MSDB database is accidentally deleted than follow below steps to rebuild the MSDB database:
  1. Stop the SQL Server service & start the command prompt with elevated administrative privilege & execute the following command:
  2. Once you start the SQL Server with trace flag 3608, you will be able to detach the msdb database. To do that, execute the following command in SQLCMD mode:
    SQLCMD -E -S DBS03 -dmaster -Q"EXEC sp_detach_db msdb"
  3. Rename the msdb data file, and execute the instmsdb.sql file from the install folder, as shown below:
    SQLCMD -E -S DBS03 -i"C:\SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Install\instmsdb.sql" -o"C:\instmsdb.out"
  4. Review the instmsdb.out for any errors and re-apply the service packs.
  5. Stop the SQL Server.
  6. Start the SQL Server normally.