Search code examples
sql-server-2008-r2ssms

Database MSDB can not be opened

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 Object Explorer

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

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

    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.