Solved

Suspect MSDB DB

Posted on 2007-04-10
3
810 Views
Last Modified: 2012-08-13
First off I should say that I am not a SQL expert so please bare with me. We are using SBS 2003. Recently, when opening the Enterprise Manager I have noticed that the msdb database is showing a "Suspect".  We have two additional active db's which are currently active. The question is how to restore the suspect db without effecting the other db's. I have tried backing the other DB's when it gets to the end of the backup however it brings up an error relating to the the msdb db.
0
Comment
Question by:nathra
3 Comments
 
LVL 16

Accepted Solution

by:
rboyd56 earned 500 total points
ID: 18883666
A suspect database has no bearing on the other 2 databases. The error occurs because when a database is backed up entries are written to several tables in teh msdb database.

The first thing that I would do is to try and reset the status of the msdb database.

From Query Analuzer run this command in the master database:

sp_resetstatus 'msdb'


Then refresh the databases in Enterprise Manager and see if teh suspect status is gone.

If not then you can restore the msdb database in Enterprise Manger or using the TSQL restore command. Restoring msdb will not affect the other active databases
0
 
LVL 11

Expert Comment

by:Ved Prakash Agrawal
ID: 18916751
place the database into emergency mode and then try to run checkdb command and try to find out which table or index coruupt.

if you can drop and recreate can resolve your problem
0
 
LVL 3

Expert Comment

by:abhijit_k
ID: 19417971
sp_resetstatus 'msdb'
After running the following Command Please restart the Service if the same doesnt work.

go to CMD Prompt
Binn for MSSQL
type-sqlservr -c -T3608
Detach MSDB by using command sp_detach_db 'MSDB'--Move the files mdf & LDF
go to install Dir for MSSQL and run the instmsdb.sql.
This should solve ur problem or if u have Previous Backup for MSDB
Just stop the SQL Server Agent and restore MSDB.
Please create a Maintenance Plan to Backup System Databases for Weekly atleast which wont take more than 25 MB space.

0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question