Suspect MSDB DB

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.
nathraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rboyd56Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ved Prakash AgrawalDatabase Consultant/Performance ArchitectCommented:
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
abhijit_kCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.