Recovering the MSDB from Suspectmode

Recovering the MSDB from Suspectmode

SYMPTOMS:

MSDB was in suspect mode as shown in bellow picture because of this we were

getting the “dms_dal_db_error” while starting the DMS. 
CAUSE:
SQL Server marks msdb database suspect if any of the device files for the database
are unavailable when it attempts to start. If the server shutdowns abnormally which
will corrupt system tables from data base that time also we can find the MSDB in
suspect mode. At startup, SQL Server attempts to obtain an exclusive lock on the
device file if SQL server is not getting that files then also we can see this problem.

Resolution:
1.     In SQL Server Enterprise Manager, right-click the server name and click Properties.
2.     On the General tab, click Startup Parameters.
3.     Add a new parameter as "-T3608" (without the quotation marks).

After you add trace flag 3608, follow these steps:

1. Stop, and then restart SQL Server.

2. Make sure that the SQL Server Agent service is not currently running.

3. Detach the msdb database as follows:

use master

go

sp_detach_db 'msdb'

go

4. Delete or rename the msdb mdf and ldf files.

5. Run the INSTMSDB.SQL script which will be in this location

<

OS Drive

>\Program Files\MicrosoftSQLserver\MSSQL\Install\INSTMSDB.SQL

6. Remove the -T3608 trace flag from the startup parameters box in

Enterprise Manager.

7. Stop, and then restart SQL Server and check that “msdb”.