Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 960
Devraj Gadhavi Avatar asked Apr 26 '13 05:04

Devraj Gadhavi


People also ask

What is the MSDB database in SQL Server?

The msdb database is a system database that is used by several SQL Server components such as the SQL Server Agent service. In addition to SQL Server Agent configuration and task information, replication, log shipping, and maintenance plan data are stored in the msdb database.

Can we shrink Msdb data file?

Can you shrink the database and transaction log for the MSDB database? The data and log files for the MSDB database can be resized. Like any other database, this isn't an operation that should be needed very often.


1 Answers

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.

like image 86
Devraj Gadhavi Avatar answered Dec 25 '22 06:12

Devraj Gadhavi