How to Deal with SQL Server 2008 R2 Error 926?

Summary: This blog will discuss error 926 that users encounter while working with SQL Server 2008 R2. It will also describe solutions on how to deal with SQL Server 2008 R2 Error 926. The blog also mentions an SQL Repair tool that can help you easily restore the database.

You write an SQL query and are ready to execute it in SQL Server 2008 R2; but as you input the code, you get the following 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).

Microsoft SQL Server 2008 R2 Error 926
Figure 1 – Microsoft SQL Server, Error: 926 Message

The error may occur during any of these operations:

  • Starting up a SQL Server instance
  • SQL database is not attached properly
  • Using the RESTORE database or RESTORE LOG commands

How to Fix SQL Server 2008 R2 Error 926?

Check the SQL Server error log to identify what caused the error. If the recovery failed due to an I/O error (a torn page) or any other hardware issue, try resolving the hardware issue to fix the error. If this doesn’t work, there must be some form of database corruption. In that case, try restoring the database from the last known database backup.

If you don’t have a backup or the backup is corrupt, set the database in EMERGENCY mode and try the DBCC CHECKDB repair operations. Doing so, will help you restore access to the database marked as suspect. But, before attempting this solution, try the following tricks to fix Microsoft SQL Server 2008 R2 Error 926 MSDB:

  1. Refresh the SQL connection.
  2. Disconnect and reconnect the SQL connection.
  3. Restart the service for MSSQLSERVER.
  4. Restart the SQL Server Management Studio (SSMS).
  5. Restartyour desktop.

If none of the above methods helpsresolve the error, perform these steps:

Step 1: Use EMERGENCY Mode to Repair a Suspect Database

Open a new query window in SSMS, and then run the following commands:

Note: You can also use the ‘sp_resetstatus’ stored procedure to turn off the suspect flag on a database. For detailed information, refer to this link.

ALTER DATABASE DB_Name SET EMERGENCY;

This command puts the database in EMERGENCY mode. In this mode, users only get read-only permission to access the database.

Note: Members of the sysadmin fixed server can only access this right.

DBCC CHECKDB (‘DB_Name’);

The above command helps check the integrity of all database objects.

ALTER DATABASE DB_Name SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

The command sets the database in ‘single user mode’.

DBCC CHECKDB (‘DB_Name’, REPAIR_ALLOW_DATA_LOSS);

This DBCC CHECKDB command will help repair the errors in SQL Server 2008 R2.

ALTER DATABASE DB_Name SET MULTI_USER;

With this command, the SQL Server database will be set in multi-user mode, which allows multiple users to access the database.

After executing all of the above commands, check if you can access the database. If you can, put the db back in normal mode.

Read this: Recover SQL Database from Emergency Mode to Normal Mode

If this step fails, proceed with the next step.

Step 2 – Move and Detach msdbFile

  1. Open Object Explorer in SSMS, right-click the connected SQL Server instance and then click Stop.
Microsoft SQL Server 2008 R2 Error 926
Figure 2 – Stop SQL Server Instance

2. Open Control panel, browse and select administrative tools, and then click Services.

Microsoft SQL Server 2008 R2 Error 926
Figure 3 – SQL Server Services

3. From services section, select SQL Server (MSSQLSERVER), make a right-click and go to Stop tab

Microsoft SQL Server 2008 R2 Error 926
Figure 4 – Stop SQL Server Service

4. Open C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA.

5. Move mdf & MSDBlog.ldf to any other folder or location.

6. Copy the files again from the new place to their original location by browsing the following path:

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA

7. In Object Explorer, right-click on the connectedSQL Server connection, and then click Start.

8. Refresh the database.

9. Now detach the msdb file

Limitations of Resolving SQL Server Error 926 Manually

There are a few downsides to using the aforementioned manual workarounds to resolve SQL error 926:

  • DBCC CHECKDB may fail to effectively repair a large-sized, severely corrupt SQL database.
  • DBCC CHECKDB ‘REPAIR_ALLOW_DATA_LOSS’ command may cause some data loss.
  • Loss of database integrity.
  • The above steps may take too much time to resolve the Microsoft SQL server error 926.

What To Do If Nothing Works?

Stellar Repair for MS SQL software can resolve any error in SQL Server that makes SQL databases inaccessible. Also, the software can help you overcome the limitationsof the above-discussed solutions:

  • It specializes in repair and recovery of large-sized, severely corrupt SQL Server databases.
  • Laced with powerful algorithms, the software can parse a SQL database and reconstruct all its objects into their original state.
  • Maintain integrity of database structure and its objects.
  • Reduces the manual efforts and time a user spends in making SQL Server databases and objects accessible.

Essentially, the software can help you recover the database marked as suspect due to SQL Server Error 926 or other SQL errors.

Read this:How to Recover MS SQL Database from Suspect Mode?

Try the demo version of the software to ascertain its effectiveness.

Free download

 Key Features of Stellar Repair for MS SQL

  • Repairs corrupt MDF and NDF files
  • Supports all versions of SQL Server
  • Recovers all database objects including tables, indexes, triggers, rules, keys, etc.
  • Allows recovery of deleted SQL Server records
  • Supports multiple file saving options like MS SQL, CSV, HTML, and XLS
  • Capable of resolving all type of corruption errors.

Conclusion

You can use manual fixes and software to deal with SQL Server 2008 R2 Error926. While the manual fixes may help you fix the error, they may take considerable time and effort in resolving the error. This increases server downtime, leading to productivity loss and chances of data loss. However, using an SQL repair software may help you restore the database in minimal time without the fear of losing data.

Comments(9)
  1. Niki L. Borger December 27, 2018
    • Eric Simson December 27, 2018
  2. Phinehas September 27, 2018
  3. Rajat July 20, 2018
  4. Raymond Douglas November 28, 2017
    • Eric Simson November 29, 2017
  5. Patty Carson November 23, 2017
    • Eric Simson November 23, 2017

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.