.comment-link {margin-left:.6em;}
A Collection of Random Thoughts
Thursday, September 07, 2006
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.
Didn't work that easy for me.. With the addition of the following I found I was able to repair the database......

1 Start SQL Server with trace flag 3608 (to allow you to detach msdb). I did this by shutting down SQL Server and then going to the 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn' directory and from a command prompt doing 'sqlservr.exe -c -T3608' (in my case I also needed a -s INSTANCENAME)

2 Use the master database and detach msdb using " sp_detach_db 'msdb' " (from SQL Server Management Studio Express right click on the master db and choose new query)

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

4 Run the instmsdb.sql script from the 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install' directory (osql -E -S servername\instancename -i instmsdb.sql)

5 Shutdown and restart SQL Server normally
Thanks guys, you helped me fix my problem.
Post a Comment

Links to this post:

Create a Link

<< Home

Powered by Blogger