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.

Answer 1

Try this

• Set the database into single user mode:

  Alter database dbname set single_user

• Now set the database into emergency mode:

  Alter database dbname set emergency

• Repair missing log file or corrupted log file with data loss.

  DBCC CHECKDB ('dbname', REAPIR_ALLOW_DATA_LOSS)

• Now set the db in multi user mode;

  Alter database dbname set multi_user

You may loss the data by using this command. It also depends on client's approval. To avoid this you may use some other dedicated software ( As Mentioned here ) to recover from suspect mode.

Answer 2

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.

Answer 3

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.