question

Equus avatar image
Equus asked

Is there a way to remove the suspect flag off of MSDB?

We had a power outage that shut down our server and when power was restored, the MSDB was flagged as suspect. Unfortunately there is no good backup of the MSDB.
msdb
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image JohnM commented ·
Is there anything in the server log about it?
0 Likes 0 ·
Equus avatar image Equus commented ·
Just that it was not repairable and was flagged as suspect. We had a backup of the MSDB that was not too old so we replaced the corrupt one with the backup. Checking everything now. Hard to believe that a power failure would do so much damage!
0 Likes 0 ·

1 Answer

·
MAXKA avatar image
MAXKA answered
The best would have been if you would have the latest backup of MSDB. So a lesson learnt from here would be always take the backups of system databases.(i.e. Master model and MSDB atleast) In you're scenario you need to rebuild the msdb since you dont have any latest backup. As a result you might be loosing any jobs, SSIS/DTS packages or any history sitting out there on earlier MSDB. Now since MSDB can't be set to emergency mode so you cannot run repair command here, so most probably you would need to recreate the MSDB. I would highlight below points on how to do it from Paul's blog: 1. 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. Do 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 2. 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) 3. Run the instmsdb.sql script in the 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install' directory 4. shutdown and restart the server without the 3608 trace flag Hope above helps!
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Equus avatar image Equus commented ·
Thank you, and yes backups are important! It was being backed up but the ball was dropped when someone transferred. Thanks for your input!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.