msdb Database Suspected\Corrupted-Solution (SQL 2000&2005)

    

  

                                                                     One day I got a call from one of my offices that some programs ie point of sale is not working and the auto back up is not running. I reached the office and checked the server for any errors I found that msdb database is corrupted. I looked for the back ups then found that I have not planned auto backups for system database. I searched over the web to find a solution for the problem. I called to all my friends for solutions. One of my friend give me a solution that worked for me.

In Sql server 2000

you start the server with trace flag 3608.In SQL Server Enterprise Manager, right-click the server name, and then click Properties.

1. On the General tab, click Startup Parameters.
2. Add the following new parameter:

-c -m -T3608

After this restart the sql server and detatch the msdb database and move the mdf and ldf files from the default folder C:\Program Files\Microsoft SQL Server\MSSQL\DAta

Then recreate msdb file by executing in new query window

instmsdb.sql (C:\Program Files\Microsoft SQL Server\MSSQL\Install)

Then remove-c -m -T3608 from the startup parameters in SQL Server Enterprise Manager

Then stop and restart the sql server 2000

and then recreate the maintenance plans

In SQL Server 2005

Detach the msdb database and install/create new msdb database. This will solve the problem. But the problem is that SQL Server 2005 does not allow to detach a system database. This will be solved by the following

another way is (you start the server with trace flag 3608. )

Start--------------Sql Server 2005-------------Configuration Tools----------SQL Server Configuration Manager

Then click on SQL server 2005 services.-----On the right side----Right click on SQL Server----Select Properties

Click on the Advance Tab and change the Parameters of Start up Parameters ie please add -m;-c;-T3608 in front of the existing parameter.

Then restart SQL server

detach the msdb database by query

”use master
go
sp_detach_db ‘msdb’
go

and click Execute

”

Then move the existing mdf and ldf files of msdb from” C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data “

Then recreate msdb files open the nstmsdb.sql in a new query window and execute.The file is located in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install

Then your msdb is restored. Before restarting the sql server remove the parameters (-m;-c;-T3608 )Then restart the sql server. Be and recreate the new maintenance plan.

via- sa paravur