Код ошибки 926

About SQL Server Error 926

Suppose 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).

Figure 1 – Microsoft SQL Server, Error: 926 Message

The above error message indicates that ‘database is marked as suspect due to failed recovery process’, which prevents the database from opening. So, you will need to repair or recover MS SQL database from suspect mode to make it accessible again.  

Following are some operations that result in SQL error 926:

• 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 first 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 recent 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. Restart your desktop.

If none of the above tricks help resolve 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, allowing 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 MSDB File

You can try to recover suspect MSDB database by moving and detaching the MSDB data and log files. For this, you can use SQL Server Management Studio (SSMS) or T-SQL:

Using SSMS

1. Open Object Explorer in SSMS, right-click the connected SQL Server instance and then click Stop.

Figure 2 – Stop SQL Server Instance

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

Figure 3 – SQL Server Services

3. In ‘Services’ dialog box, locate and right-click on SQL Server (MSSQLSERVER), and then click Stop.

Figure 4 – Stop SQL Server Service

4. Open C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATA.

5. Move the MSDB data file (.mdf) & log file (.ldf) to any other folder or location.

6. Copy the .mdf and .ldf files from the new location to their original location by browsing the following path:

C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATA

7. Restart SQL Server Service.

8. Refresh the database, and detach the MSDB file. 

Using T-SQL

You can also recover the MSDB database in suspect mode by executing the below T-SQL query:

EXEC sp_resetstatus ‘db_name’ ALTER DATABASE db_name SET EMERGENCY DBCC CHECKDB (‘db_name’) ALTER DATABASE db_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE DBCC CHECKDB (‘db_name’, REPAIR_ALLOW_DATA_LOSS) ALTER DATABASE db_name SET MULTI_USER

Note: Replace db_name with the name of your database marked as suspect.       

The above code will turn off the suspect flag on the database and put it in emergency mode. Next, it will bring the database to single-user mode, repair it, and will put the database to multi-user mode.

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 data integrity.
• The above steps may take too much time to resolve the Microsoft SQL server error 926.

Alternative Solution to Fix SQL Server 2008 R2 Error 926

SQL database recovery software can resolve any error, including error 926 in SQL Server causing databases to become inaccessible.

Also, the SQL database repair software can help you overcome the limitations of the above-discussed solutions, here’s how:

• It specializes in the 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 the integrity of database structure and its objects.
• Reduces the manual efforts and time a user spends in making SQL Server databases and objects accessible.
• Recommended by Microsoft SQL MVPs. Check the MVP review from here.

You can download the demo version of the SQL database repair software to ascertain its effectiveness.

 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 Error 926. 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 SQL repair software may help you restore the database in minimal time without the fear of losing any data.

Posted: January 6, 2015 by Virendra Yaduvanshi in Database Administrator
Tags: Database MSDB cannot open, Database suspect by recovery, MSDB marked as suspect, MSDB problem, MSDB stuck, SQL error 926, SQL Server error 926, SQL server instance starting problem in SSMS, SSMS Error, SSMS error 926

Get Rid of Suspect SQL Server Error 926 and 945

SQL Server databases can experience a lot of issues with its integrity and if there is any inconsistency with the database in its Read or Write operation can cause a fatal error. SQL Server error 926 and 945 also belong to such error types where certain associated files are either missing or has some other issues. The case is rare but can happen while connecting the SQL Instance using SA login. What happens is when you try to connect it, an error message is thrown depicting that the ‘msdb’ cannot be opened and it is marked as suspect. Both of SQL error 926 and error 945 are caused due to different reasons. We will discuss about these errors one by one.

SQL Error 926

Severity Level 14

Error Message: SQLDatabase ‘%.*ls’ cannot be opened. This database is marked as SUSPECT by recovery. Refer SQL Server errorlog for more details.

Description:

Such type of error resembles that the database has integrity issues and that is why the database is marked as SUSPECT. It might have failed the recovery process which brings the database to consistent state. SQL Server Error 926 can be displayed while performing the mentioned operations like; while starting the Microsoft SQL Server instance, or while attaching any database or while performing RESTORE or RESTORE LOG procedures.

Error Resolution:

SQL Error 926 can be rectified at user’s end and the database can be then accessed without any error. As soon as this error message is detected, one can check for Microsoft SQL Server error log and check for the root cause for SQL Server error 926 to occur. In case recovery was not possible due to other reasons like I/O error, hardware issue, or torn page, etc.

For such error one can consider the integrity check utility DBCC CHECKDB which will report the error and provide a solution for it. One can opt to restore the backup if updated backup is available. In case backup is not available or the integrity check tool is unable to resolve the problem, one can contact primary support provider or can also contact the error logs for SQL Server.

SQL Server Error 945

Error 945

Severity Level 16

Error Message:

SQL Server Database ‘%.*ls’ is not opening because of inaccessible files. This can also be caused if there is not enough disk space or memory on the system then also this error can be generated. See the error log of SQL Server for more details.

Description:

SQL server error 945 is displayed when the database is marked as IsShutdown. This error occurs when a database cannot be recovered due to missing files. The error can also be caused as some resource error was not corrected successfully.

SQL Server Error 945 Resolution:

Check if database is flagged as IsShutdown with DATABASEPROPERTY. Afterwards, you can examine the cause of error by checking the errorlog and perform below mentioned steps;

If data/log files are missing;

• Search for the missing files, and bring the database OFFLINE.
• Use ALTER DATABASE in order to bring it ONLINE.

If log space is not enough;

• Utilize sp_add_log_file_recover_suspect_db() for adding new log file.

Database will be recovered and will be mounted online.

In case memory is not sufficient;

While recovering the multiple databases, this error can be displayed. One can retry the operation which might fix this issue.

• Try the operation again with ALTER DATABASE for bringing the .db OFFLINE.
• Then utilize ALTER DATABASE for bringing .db ONLINE.

Common Solutions – SQL Server Error 926& 945

Both the SQL Server error 926 and error 945 can be correct by these following steps:

Hardware Issues:

Windows system can have system issues and if there is corruption problem, databases mounted on the system will also be inaccessible. In such condition one can contact the Microsoft product service provider and fix the components.

Fetch Database from Backup:

In case users are having an updated backup of the SQL databases then one can easily restore the databases from the backup. Once the databases are restored, erroneous messages will not be displayed.

Repair Solution:

The very first step which must be taken is trying the inbuilt integrity check DBCC CHECKDB command. This integrity utility will check if there is any error issue and will report it to you and if the error still persist after applying above mentioned procedures then one can also go with third party utilities to recover SQL database from suspect mode and fix SQL Server error 926.

• Распечатать