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
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.
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 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.
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.
Open new query window
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.)
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.)
DBCC checkdb('DB_Name');
(Explanation : Check the integrity among all the objects.)
ALTER DATABASE DB_Name SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
(Explanation : Set the database to single user mode.)
DBCC CheckDB ('DB_Name', REPAIR_ALLOW_DATA_LOSS);
(Explanation : Repair the errors)
ALTER DATABASE DB_Name SET MULTI_USER;
(Explanation : Set the database to multi user mode, so that it can now be accessed by others.)
In Object Explorer --> The opened connection item --> rightclick --> Stop
Open Control Panel --> Administrative Tools --> Services
Select Sql Server (MSSQLSERVER)
item from services --> rightclick --> Stop
Open C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA
Move MSDBData.mdf
& MSDBlog.ldf
to any other place
Then Copy this Files Again from new place and put it in older place
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA
In opened connection in object Explorer --> rightclick --> Start
Then Refresh DataBase.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With