SQL 2000 : Database 'msdb' cannot be opened OR 'msdb' database is marked as suspect

SQL 2000 : Database 'msdb' cannot be opened OR 'msdb' database is marked as suspect
Hi ALL,



          I got the following error in some servers and I want to share the 
problem with you.



1- Screen shot to show the error





2- My msdb database is marked as suspect. This means that my maintenance 
plan doesn't work, The msdb database is the database that holds information 
about SQL Server Agent jobs (of which the maintenance plan is one). It also 
have tables for backup history of databases. It's definitely a database that 
you should back up regularly. It's usually not that big, so it sounds a 
little funny that you would run of disk space because of it.



3-Work out what happened to make msdb go suspect.

o    Take a look at the SQL Server errorlog (if you have a single instance 
installed in the default path, this will be in the 'C: \Program 
Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG' directory) and the Windows 
application event log (use the Event Viewer from the Administrative Tools 
menu from Start). Look for any indications of disk or IO problems.

o    Alternatively, do you know if any unusual event happened to the server, 
like an unexpected power cycle? If this happened, and you have write-caching 
enabled on your drives, this can cause a problem.

o    Whatever happened, you should work to understand it so that you can 
take any necessary steps to prevent it happening again.

4-Because the database is suspect, and msdb cannot be put into emergency 
mode, you're not going to be able to repair the database so you'll need to 
recreate it (and then recreate any scheduled jobs). Here's how to do it :

o    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 'start sqlservr.exe -c -T3608'

o    Use the master database and detach msdb using "sp_detach_db 'msdb'"

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

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

o    Shutdown and restart SQL Server normally

Inform me if you have any suggestions.
date: Thu, 8 May 2008 15:55:03 +0200 author: yaser