Database MSDB can not be opened

Question

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

1. Refreshing the connection
2. Reconnecting the connection
3. Restarting the service Sql Server (MSSQLSERVER).
4. Restarting the SQL Server Management Studio
5. 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.

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

1. Open new query window

2. 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.)

3. 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.)

4. DBCC checkdb('DB_Name'); (Explanation : Check the integrity among all the objects.)

5. ALTER DATABASE DB_Name SET SINGLE_USER WITH ROLLBACK IMMEDIATE; (Explanation : Set the database to single user mode.)

6. DBCC CheckDB ('DB_Name', REPAIR_ALLOW_DATA_LOSS); (Explanation : Repair the errors)

7. 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

1. In Object Explorer --> The opened connection item --> rightclick --> Stop

2. Open Control Panel --> Administrative Tools --> Services

3. Select Sql Server (MSSQLSERVER) item from services --> rightclick --> Stop

4. Open C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA

5. Move MSDBData.mdf & MSDBlog.ldf to any other place

6. Then Copy this Files Again from new place and put it in older place

   C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA

7. In opened connection in object Explorer --> rightclick --> Start

8. Then Refresh DataBase.

9. 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.

Comments

[Mike J]

Please note that the msdb contains the jobs run by the SQL agent. You will lose this information if you just grab these .ldf and .mdf files from any SQL Server. Your best bet is to copy the msdb files from any other working machine AND restore the MSDB from a recent backup of the problematic instance, ideally one prior to the incident.

[sourabh devpura]

G8 . When my client call me for this . i was find every where and didn't get the ans . finally your and work for me . thanks for this g8 job