Fixing Suspect SQL databases
Disclaimer: This probably should not be done on a production system! I have several SQL servers for use by our Developers, and none of them have any critical data on them. If you have a database that has been marked suspect by SQL, the best course of action that I am aware of is always to restore your data from backup.

I used the instructions provided byPaul Randal, who is a Lead Program Manager for SQL, at http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/06/619304.aspx

In my case, the problem database was the msdb database. With this db marked as suspect, the SQL Agent service would not start. Checking the SQL Agent log indicated this was the reason. As Paul mentions, since msdb is a system database, you can't repair it like you would others. In order to fix this problem, I had to start SQL by using:

start sqlservr.exe -c -T3608

This starts up SQL in a special mode where it only attaches to the master database. You can then run the instmsdb sql script to reinstall the msdb database. Running this script will first detach, then remove the msdb database files, then re-create and re-populate them. The proceure for doing this is quite simple. From the \sql\install directory, run the following command.

osql -E -S servername -i instmsdb.sql

Note: when you start SQL with this flag, the SQL service itself does not appear started. Instead, a separate cmd window will pop up with the SQL information. When you are ready to exit, you can simply hit Ctrl-C. You will then see a prompt asking you if you want to shut down the SQL server.

Wait a while (it took approx. 5 minutes for mine) and once it completes, stop and restart the SQL service so that it is no longer running in this special mode, and you should be all set. With this procedure completed, we were able to start the SQL agent without problems.
- posted by Ben Winzenz @ 10:13 PM