Solved

Suspect MSDB DB

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Help 27 41
Return 0 on SQL count 24 28
SSRS Enable Remote Errors 4 23
sqlserver get datetime field and create a string 5 17
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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

786 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