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.

Complainant

Answer 1

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.

Complainant

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

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 – Treiber

ALTER DATABASE DB_Name SET EMERGENCY; this is taking forever – Stocks

@MikeJ what if someone have only one machine ? – Stocks

Answer 2

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.

Ballonet

Answer 3

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.

Iphigeniah