Monday, February 20, 2012

MSDB Database SUSPECT - RESET PROCEDURE ?

I have a regional server running SQL2000 and Merge replication, the MSDB, NORTHWIND and 2 User DB's have been marked suspect. I cannot get into query analyzer etc to run the necessary scripts as it is all suspect.

Any ideas on how I can reset the status on the MSDB database??
Unfortunately there is no backup of either of the 2 system databases :(

** ANY ASSISTANCE WOULD BE GREATLY APPRECIATED **

JReset database with "suspect" status:

Do the following steps from the Query Analyzer:

1) Use Master
GO
EXEC sp_resetstatus <dbname>
2) Stop & Start the SQL Server
3) Run DBCC CHECKDB
4) Do the Full database backup (Master DB included)

If the above steps didn't resolve the problem, run the query below:

Use Master
GO
EXEC sp_configure 'allow updates', 1
GO
Reconfigure with override
GO

Do the step 1 ~ 4 above, then:

Use Master
GO
EXEC sp_configure 'allow updates', 0 -- to turn off
GO
Reconfigure with override
GO

NOTE: If the database become "suspect" because it ran out of disk space, you have to do the step #1 above, then use the "ALTER DATABASE ..." to add more data file (or log file), Stop & start the SQL Server and finish with step #3 & #4 as above.

Good luck.|||Valid reponse by SVT and make sure to have regular backups for SYSTEM Databases too in future.

No comments:

Post a Comment