User Query 1: “This is my first post here and I am in a bit of trouble I guess. Yesterday, when I attempted to access the database from Visual Studio to work on my SQL Server, I got a message that says Database ‘msdb’ cannot be opened. It has been marked SUSPECT by recovery. Error: 926.” I have no clue why this error is appearing. This is literally stopping me from accessing the database. I use the 2008 version of SQL Server. Please tell me how to get rid of this error message.”
User Query 2: “I am a user of SQL Server 2008 R2 on 64-bit Windows 7 system. Currently, I am unable to find any of the several databases in object explorer. Though the new query window appears and lets me perform queries, those are not visible to me. During database exploration, I get this error message: “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 restarting the database and similar commonly used tricks. But none of them worked. What should be my next step to fix this annoying problem?”
SQL Server 2008 R2 is quite prone to numerous errors. One of them is Database ‘msdb’ Cannot be Opened. Error 926.” Issues like offline device, dirty shutdown, database corruption, unavailable database file can cause this error and it prevents users from accessing any database object. Do not worry, as we will be offering some of the reliable solutions to this error. Continue reading this post to get the detailed procedure of the techniques.
Three Trusted Solutions to Fix Database ‘msdb’ Cannot be Opened Error
If you are one of the unlucky people who are receiving this error, try these techniques. Here, we will be presenting three different methods that are often used to resolve this issue. Users can take the help of any one or all of these methods.
1. Run DBCC CHCKDB Command
a. Open a new query window.
b. Run “EXEC sp_resetstatus ‘DB_Name’” command. Here, ‘sp_resetstatus’ will reset the suspect status of that particular database. In sys.databases, the status column of the database will get updated. Remember, you will be able to perform this command only if you logged in with system admin privileges.
c. Now run “ALTER DATABASE DB_Name SET EMERGENCY.” The database will be switched to emergency mode, which is actually the READ_ONLY copy. Only sysadmin members with designated server roles can access it.
d. Run the script “DBCC checkdb(‘DB_Name’)” for integrity verification among all database objects.
e. Turn the database in the single user mode by ALTER DATABASE DB_Name SET SINGLE_USER WITH ROLLBACK IMMEDIATE command.
f. Now, you can repair the data errors and corruptions by running DBCC CheckDB (‘DB_Name’, REPAIR_ALLOW_DATA_LOSS)
g. Set the database back in multi-user mode by “ALTER DATABASE DB_Name SET MULTI_USERDetails”.
2. Stop and Restart the Process
a. Open Object Explorer and select the connection item. Right-click on it and select Stop Object Explorer option.
b. Go to Control Panel>> Administrative Tools>> Services Control Panel>> Administrative Tools>> Services.
c. From the list of services, Select SQL Server MSSQLSERVER to right-click on it. Choose Stop option.
d. Navigate to this location:
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA
e. Transfer MSDBData.mdf & MSDBlog.ldf files to some other location.
f. Now, copy those files from the new location and paste in the old location.
g. Open Object Explorer and right-click to select Start option.
h. Refresh your database and detach the MSDB file.
3. Remove Corruption using SQL Recovery Tool
Ratherthan trying these manual tricks, it is better to fix all thecorruption issues of database by using SQL Recovery Tool. This application is capable of removing all types of corruptions, thanks to its Advance scanning mode. This utility will work perfectly on all the latest versions of SQL Server including SQL Server 2019. Here is how you can run this application to resolve this problem:
a. Start the tool and add the MDF file.
b. Select Advance scan and enter the server version.
c. Preview all the database components.
d. Save the corruption-free MDF file.
Database ‘msdb’ Cannot be Opened. It Has Been Marked Suspect by Recovery is a common error message that can significantly harm the day to day activity of SQL Server users. Here, we have discussed various techniques to remove this problem for good. We expect that users will get benefited by following the instructional guide provided here.