Search

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:
    NET START MSSQLSERVER /T3608
  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.

2 comments: