Database MSDB can not be opened
sql-server-2008-r2, ssms
Solution
I found my answer in this link.
EDIT : Including both the solutions from link because of possible Linkrot in future.
Login with `sa` account, for both the solutions.
Solution 1
Open new query window
`EXEC sp_resetstatus 'DB_Name';` (Explanation :`sp_resetstatus` turns off the suspect flag on a database. This procedure updates the mode and status columns of the named database in `sys.databases`. Also note that only logins having sysadmin privileges can perform this.)
`ALTER DATABASE DB_Name SET EMERGENCY;` (Explanation : Once the database is set to EMERGENCY mode it becomes a READ_ONLY copy and only members of sysadmin fixed server roles have privileges to access it.)
`DBCC checkdb('DB_Name');` (Explanation : Check the integrity among all the objects.)
`ALTER DATABASE DB_Name SET SINGLE_USER WITH ROLLBACK IMMEDIATE;` (Explanation : Set the database to single user mode.)
`DBCC CheckDB ('DB_Name', REPAIR_ALLOW_DATA_LOSS);` (Explanation : Repair the errors)
`ALTER DATABASE DB_Name SET MULTI_USER;` (Explanation : Set the database to multi user mode, so that it can now be accessed by others.)
Solution 2
In Object Explorer --> The opened connection item --> rightclick --> Stop
Open Control Panel --> Administrative Tools --> Services
Select `Sql Server (MSSQLSERVER)` item from services --> rightclick --> Stop
Open `C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA`
Move `MSDBData.mdf` & `MSDBlog.ldf` to any other place
Then Copy this Files Again from new place and put it in older place
`C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA`
In opened connection in object Explorer --> rightclick --> Start
Then Refresh DataBase.
Then you can Detach the MSDB File
The 2nd solution worked for me.
Note : I had to get "msdb" database mdf and ldf files from another working machine to get it working.
Problem
I have got this problem in local instance of SQL Server 2008 R2 on my machine. There are several databases on this instance. But I am not able to see any of them from the object explorer. I am able to query my databases from the new query window. But not able to see any of them. Whenever I try to explore the databases I get this error : Database 'msdb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. (Microsoft SQL Server, Error: 926). I have tried - Refreshing the connection - Reconnecting the connection - Restarting the service Sql Server (MSSQLSERVER). - Restarting the SQL Server Management Studio - Restarting my machine I have also tried combinations of above, but nothing works. My operating system is Windows 7 Ultimate (64 bit). SQL Server Management Studio Version is 10.50.2500.0.