MSDB Corrupted

Recreate Corrupt MSDB database

If your msdb goes suspect then you have two choices, restore it from a backup or recreate it (and then recreate any scheduled jobs). Obviously everyone has a comprehensive and valid set of backups, right? If only...

Of course, the very first thing you do is work out why it went suspect in the first place and take any necessary steps to stop it happening again.

Now you'd hope that if you don't have a valid msdb backup then you can at least run repair on it and so you don't lose everything in there. Well, that works as long as the transaction log isn't damaged. Ok, but then surely we can stick the database into the now-documented emergency mode (alter database dbname set emergency) and run emergency mode repair? (dbcc checkdb (dbname, repair_allow_data_loss) in emergency mode). Nope, msdb can't be put into emergency mode.

So, you're out of options and you're going to have to recreate msdb. Here's what to do (change the directory paths to suit your installation):

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. I did this by shutting down the server, navigating to the directory 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn' and doing the following: start sqlservr.exe -c -T3608

Move or rename the damaged msdb files (msdbdata.mdf and msdblog.ldf in the 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data' directory)

Run the instmsdb.sql script in the 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install' directory

Shutdown and restart the server without the 3608 trace flag

This works on SQL Server 2000 as well.

Rebulding MSDB in SQL Server 2005

Seems that because of some diskspace issures we had in April, my MSSQL configure got hosed and the server hasn’t been able to perform any backups nor has the sql server agent been running. This was due to the fact that the MSDB database has been corrupted.

The following are the steps that I took to get everything back up and running. I will warn you that by doing these steps, you will lose all you Maintenance Plans that you had and you will have to recreate them. Figure that this is a small price to pay because let’s face it, you got to have backups.

I would strongly suggest that you don’t be an idiot like me and make sure that you backup your msdb database. However if you are like me and didn’t do this, these steps should get everything back on track.

1. Put MSSQL into single user mode
   1. Click -> START -> Microsoft SQL Server 2005 -> Configuration Tools – > SQL Server Configuration Manager
      
   2. Right click on SQL Server and choose Properties
   3. Click on the Advanced tab. Under Startup Parameters you will be adding the following parameters to the beginning of the string: -m;-c;-T3608
2. Restart SQL Server
3. Connect to SQL server through the Management Console. From this point on we will be using TSQL to issue the commands so click the New Query button on the top left. At this point you should be in the master database inside the query window.
4. Detach the MSDB database using the following commands:
   use master
   go
   sp_detach_db ‘msdb’
   go
   and click Execute
5. We need to move (or rename, I prefer moving them) the existing MDF and LDF files for the MSDB database so that we can recreate it.
   1. Usually these files are located in the following directory:
      C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DataYour’s might differ.
   2. Move (or rename) the MSDBDATA.mdf and MSDBLOG.ldf files.
6. Back to the Management Studio. Open up the instmsdb.sql file in a new query window. This file is usually located in the following directory:
   C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install
7. Execute the file. If you see any errors about xp_cmdshell, just ignore them. They are common and the script will take care of it.
8. At this point you should have your MSDB database restored. The only thing left is cleanup.
9. Execute the following command to make sure that xp_cmdshell is once again set to disable for security reasons:
   EXEC sp_configure ‘show advanced options’, 1
   GO
   RECONFIGURE WITH OVERRIDE
   GO
   EXEC sp_configure ‘xp_cmdshell’, 0
   GO
   RECONFIGURE WITH OVERRIDE
   GO
10. Shutdown SQL Server
11. Go back into your Startup Paremeters for your server in the SQL Server Configuration Manager and removed the -c;-m;-T3608 parameters we added earlier.
12. Restart SQL Server

Everything should be cool at this point and you’ll be able to recreate any Maintenance Plans and Jobs.