Problem: 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
- Refreshing the connection
- Reconnecting the connection
- Restarting the service Sql Server (MSSQLSERVER).
- Restarting the SQL Server Management Studio
- 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.
- Open new query window
EXEC sp_resetstatus 'DB_Name';(Explanation :
sp_resetstatusturns 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_USERDetails;(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
Sql Server (MSSQLSERVER)item from services –> rightclick –> Stop
- Open C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA
MSDBlog.ldfto 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.
database ‘msdb’ cannot be opened. it has been marked suspect by recovery 2008 error code 926
Repair SQL Server Database marked as Suspect or Corrupted
I am using SQL server 2008, i got suddenly today an error while i was accessing database from visual studio the error is database ‘msdb’ cannot be opened. it has been marked suspect by recovery 2008 error code 926
don’t know what to do to get out from this type of error. any one have any idea please let me know
There can be many reasons for a SQL Server database to go in a suspect mode when you connect to it – such as the device going offline, unavailability of database files, improper shutdown etc. Consider that you have a database named ‘test’ which is in suspect mode
You can bring it online using the following steps:
Reset the suspect flag
Set the database to emergency mode so that it becomes read only and not accessible to others
Check the integrity among all the objects
Set the database to single user mode
Repair the errors
Set the database to multi user mode, so that it can now be accessed by others
Here is the code to do the above tasks:
Here’s the same code for you to try out
EXEC sp_resetstatus ‘test’
ALTER DATABASE test SET EMERGENCY
DBCC CheckDB (‘test’)
ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (‘test’, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE test SET MULTI_USER
The fastest way is to restore the MSDB database, but if it’s your first time doing that, here’s an easier shortcut.
- Restore the MSDB backup onto an existing (working) database server, but use a different database name than MSDB. The restore should go quickly (because MSDB is typically very small), and you can then verify that your objects are in there.
- Detach the database, and copy the mdf/ldf files over to the broken instance.
- Move the broken mdf/ldf files somewhere for safekeeping, and replace them with your newly restored mdf/ldf files.
Start the SQL Server instance again, and you’re set.
One gotcha – in step 1, make sure you’re restoring onto the same version of SQL Server. If the broken server is SQL Server 2005, don’t do the restore on SQL Server 2012, because the SQL 2005 instance won’t be able to attach databases that have been touched by a newer version of SQL Server.
One of our clients’ SQL Servers got corrupted. we were forced to uninstall all instances and all SQL Server programs in add/remove programs and then reinstall SQL Server because their backups were failing and useless.
I was able to re-attach the user databases but now I need to recover the SQL Server agent jobs, so first I tried creating a user database to hold the old msdb data called
MSDBData_OLD, stopping the SQL Server service, replacing the
.mdf file with the corrupted msdb’s
.mdf file and deleteing the
.ldf file, which would normally result in the database getting marked SUSPECT, and I could take it from there, but when I start the service again and click the database it says:
The database MSDBData_OLD is not accessible (ObjectExplorer)
Next I tried attaching with the log file and I got this:
Finally, I tried simply attaching the msdb database as a user database, but when I do, I get:
File activation failure. The physical file name “D:\SQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf” may be incorrect. The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
D:\SQL\MSSQL10_50_.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf is the path of the old and restored SQL Server
msdb database log file.
We are running SQL Server 2008 R2 64 bit SP1.
Is there any way to recover from this or am I forced to rebuild the jobs from scratch?