Fix Recover MSDB Suspect in SQL 2005 / SQL 2008

Solution for MSDB Suspect in SQL

Solution 1

1. Reset the suspect flag
2. Set the database to emergency mode so that it becomes read only and not accessible to others
3. Check the integrity among all the objects
4. Set the database to single user mode
5. Repair the errors
6. Set the database to multi user mode, so that it can now be accessed by others

Here is the code to do the above tasks:

EXEC sp_resetstatus 'test'

ALTER DATABASE test SET EMERGENCY

DBCC CheckDB ('test')

ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('test', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE test SET MULTI_USER

Solution 2 Best Method

• What instantly fixed my issue was to replace existing MSDBData.mdf & MSDBlog.ldf files in 

C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA

• I got these 2 files copied from another working machine.
• Stopped the SQL service running in my machine
• Removed the above existing 2 files from their location and added the new 2 copied. 
• Once I restarted the service , issues has been fixed.