Solution for MSDB Suspect in SQL
Solution 1
- Reset the suspect flag
- Set the database to emergency mode so that it becomes read only and not accessible to others
- Check the integrity among all the objects
- Set the database to single user mode
- Repair the errors
- 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.
Post a Comment
Post a Comment