Solved

Suspect MSDB DB

Posted on 2007-04-10
3
805 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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now