Ошибка 605 sql

SQL Server Error 605: Learn Its Symptoms, Causes, Troubleshoot Approaches

“Hello there! I use SQL Server 2008 environment. Recently, while running some queries, the session got disconnected. Then an error message appeared that said “Msg 605 Level 21 State 3 Line 1”. I have tried running the query again but every time getting the same error message. Please help me get rid of this annoying SQL Server error 605.”

Are you also getting 605 error while running a query? Do you want to have a permanent solution to this error message? If yes, then you have landed on the right page. This write-up will discuss ins and outs of this error and provide users a complete guide to SQL fatal error 605.

Table of Content

• Quick Steps Solution
• SQL Error 605 Symptoms
• Causes of SQL Fatal Error 605
• Manual Solution to Fix 605 Error
• Automated Solution
• Conclusion
• FAQs

Warning Fatal Error 605 is another error that SQL Server 2008 and 2008/R2 users face quite often. This error gets generated when a query is made to read a database page, where the allocation unit cited in the page header (also known as actual allocation unit) does not coincide with the expected allocation unit as per the table(s) mentioned in the query. However, this error is not a trivial one as it ultimately bars users from running the query. That is why this error is the topic of discussion for this write-up. We will begin with the symptoms of error 605 in SQL Server.

Also Read: How to resolve SQL Server error 3456?

SQL Server Error 605 – Indications/ Symptoms

There can be two types of the severity level of  SQL warning fatal error 605. One is severity 21 and another one is severity 12. If the severity level is 21, the user session gets disconnected. The entry for this error is written as EventID=605 into both the SQL ERRORLOG and Windows Application Event Log. In the case of severity 12, users will only see an error message. Here is an example of Microsoft SQL error 605 message.

Here, the first allocation unit is the actual allocation unit and the latter one is the expected allocation unit. An allocation unit is an ID that is used to identify the index and object to which a page belongs.

Recommend Solution: Use the SQL Recovery software if user wants to fix all kind of SQL errors. This application is a secure solution for SQL database corruption repair and save data in a healthy state. User can repair corrupt MDF file of SQL Server 2019, 2017, 2016, 2014, 2012, etc.

Download Now Purchase Now

What Causes SQL Fatal Error 605?

As we have just mentioned, Microsoft SQL Server error 605 severity 21 states 3 can have two different severity levels. Let’s know the reasons behind the two levels of error.

• Severity 21: Error 605 with Severity 21 happens if there is a page level problem, or SQL Server engine having trouble while determining the expected allocation unit
• Severity 12: If the query uses ‘read uncommitted isolation level’ or NOLOCK query hint and therefore fails, SQL error 605 of Severity 12 occurs.

How to Fix SQL Server Error 605 Manually?

Error 605 Severity 21: Run DBCC CHECKDB and see what comes up.

If it reports an error with message 2533, in that case, the page is corrupt or incorrect. To solve the issue, restore from a last good backup. If that is unavailable, run DBCC CHECKDB again with the Repair option. You can also go to the root of this problem by performing the following steps:

• Use SQLIOSIM tool that comes with SQL Server 2008 and SQL Server 2008 R2 to find out any system level or hardware problem.
• Make sure that PAGE_VERIFY=CHECKSUM database option is turned on. Though error message 605 can occur while checksum is enabled, confirming it will prove that the page was indeed modified incorrectly within the input-output system.
• Restore a clean database backup and transaction log backups that were taken before the SQL fatal error 605 occurred.

If DBCC CHECKDB does not come up with an error and the problem keeps occurring, contact Microsoft Technical Support team.

Error 605 with Severity 12: Follow these instructions if encountered this error:

• Do not use NOLOCK hint or ‘a read uncommitted isolation level’
• Keep retrying the query until the error disappears
• Do not make any changes to the infected tables when you are running the query

Looking For a Sure-shot Solution? Try SQL Recovery Tool

Since SQL Server error 605 occurs due to page level corruption, users can fix SQL Server database error using the best SQL Recovery Software. This tool is a sure-shot solution compared to the manual approaches mentioned in the earlier section. Rather than checking all the manual tricks one by one without knowing which one will work, it is better to opt for the tool. No matter how severe the corruption is, the utility is capable of fixing all kinds of page-level corruption of SQL Server. It will work on SQL Server 2008/ 2008 R2, along with 2019, 2017, 2016, 2014, 2012, 2005, 2000.

Final Remarks

SQL Server users, especially SQL 2008 users, encounter error 605 due to page level corruption. Though there are various manual solutions to fix this issue, none of them can be trusted wholeheartedly. If users want to resolve SQL Server error 605 permanently, they should choose SQL Database Recovery Tool. This application will eradicate this error and allow users to continue using their SQL server database smoothly.

Frequently Asked Questions

Read time 6 minutes

The SQL Server can contain multiple databases in it and when the user uses SQL Server Management Studio to open the database, then whole information is available for usage and modification. Sometimes, the application shows an error while opening the database and it can stop you from accessing the database content.

“Msg 605, Level 21, State 3, Line 1 – Attempt to fetch logical page (1:224) in database 11 failed. It belongs to allocation unit 72058253744865280, not to 72057594040287232.”

or

Are you familiar with this error message? Absolutely, this error is familiar to every user who has ever tried to access the damaged MDF files which are inaccessible from SQL database. However, there exact reason of existence is merely unknown to all users.

Reason of Occurrence of Error

Error Msg 605 appears when a user tries to log through the system, which is not system administrator. The error appears in RED color. Usually when severity level is 21, the user session gets terminated and the error is registered in SQL error log and Windows Application Event Log as Event ID 605.

The severity level could be 12 only when it is returned from client application. In fact, severity of this error can be Level 12, if the query flops in situation when it is utilizing read uncommitted isolation level.

Irrespective of the technical know-how about the Error Msg 605, a user always looks to get a prominent solution to the problem.

Resolution for Error Msg 605

In general, you as a user can resolve the issue, when the severity level is 21 with the help of a backup. Initially, you require to gather right information about the data file and page number given in error message, you must use DBCC PAGE command.

dbcc traceon(3604)
dbcc page(‘MyDatabaseName’,1, 224,3)
dbcc traceoff(3604)

NOTE: You could very well make use of option 0,1,2,3 as the last parameter (bold 3) of DBCC PAGE command. You have two different options to choose from:

1. Restore that page from a complete Database backup
2. Try DBCC commands

Although, experts recommend to opt for the1st option, but just in case you don’t have last full backup, the you could easily opt for a second way. Given below TSQL, will help in restore the page from the last complete database backup

RESTORE DATABASE MyDatabaseName
PAGE = ‘1:224’
FROM DISK = ‘D:\MyDatabaseName.bak’
WITH NORECOVERY

But talking about the situation when you don’t have the complete backup of your database, then the best option you need to work on is usage of a DBCC CHECKDB command. In case of no backup, first you require to verify about the severity level.

DBCC CHECKDB (‘MyDatabaseName’) WITH NO_INFOMSGS

After executing the above command, it may recommend REPAIR_REBUILD, but also need to note that if you see repair level REPAIR_ALLOW_DATA_LOSS, unfortunately you may require losing some of your data. Use the below given command based on the Repair Level.

1. ALTER DATABASE MyDatabaseName SET SINGLE_USER
   GO
   DBCC CHECKDB(‘MyDatabaseName’, REPAIR_REBUILD)
   GO
   ALTER database MyDatabaseName SET MULTI_USER
   GO
2. ALTER DATABASE MyDatabaseName SET SINGLE_USER
   GO
   DBCC CHECKDB(‘MyDatabaseName’, REPAIR_ALLOW_DATA_LOSS)
   GO
   ALTER database MyDatabaseName SET MULTI_USER

But, the resolution to Error Msg 605 may slightly differ in case of severity level 12. When severity level 2 is encountered, user is expected to follow these steps:

1. Just ignore uncommitted isolation level (or NOLOCK hint).
2. Do not make any changes while running the SQL query.
3. Do not make unnecessary interruptions.

These steps may give you some relief from Error Msg 605, but still a user cannot expect high-end qualitative results from such tool because there is a chance that the SQL server edition may not favor the retrieval process.

There are a wide range of SQL servers in which there are numerous linked database. MS SQL Server 2019/2017/2016/2014/2012/2008 R2/2008/2005/2000 can be used to store the MDF files. Suppose the conventional approach to retrieve the database does not support the SQL Server 2014, then it would become impossible to fetch and secure the data. Therefore, it is necessary to take assistance from a tool that can take guarantee recovery of MDF files.

Making Dedicated SQL Recovery

Dedicated recovery of damaged pages of MDF files means recovery of file components like Triggers, Rules, Functions, Tables, and Stored Procedures etc. To this extent most software promises revered recovery, but only few of them accomplish their task perfectly. If you wish to preview the recovered file components, then you are recommended to install the software.

The relevance of the software is not limited to direct recovery of MDF files from bad state, but it extends up to facilitation for ‘Preview’ of recovered components and utterly different saving modes that makes the tool unique in performance. The tool swiftly recovers Triggers, Rules, Functions, Tables, and Stored Procedures etc. creating backup of recovered files in a script form. During entire process the data integrity is maintained. The tool also provides an option to repair multiple MDF files in a single instant.

It is really different – Take a Trial

Apart from a simple statement about tool’s performance, the features can also portray an image of the tool. Some significant features of the software are it uphold data integrity and file structure. The tool is also effective in repairing multiple MDF files in a single batch cycle. Just to evaluate the exact performance of the tool, you can download the trial version of Kernel for SQL Recovery that lets you to witness the performance with a limitation of not saving the recovered results.

In this article, we will look into the details of SQL Server Fatal Error 605 and the available fixes. At Bobcares, with our Microsoft SQL Server Support Services, we can handle your MSSQLServer issues.

MS SQL Server Fatal Error 605

Error 605 appears when a page or allocation corruption occurs in a particular database. When reading pages from a table, SQL Server identifies corruption by tracking page links or by using the Index Allocation Map (IAM). All pages assigned to a table must belong to one of the table’s allocation units.

The exception is thrown if the allocation unit ID in the page header does not match an allocation unit ID connected with the table. The first allocation unit ID provided in the error response is the one found in the page header, and the second is the one connected with the table. The errors can be:

• Severity 21: While detecting the expected allocation, this error message can cause major problems with the database page or the SQL Server engine.
• Severity 12: If the query execution fails when utilizing the read uncommitted isolation level. Or the NOLOCK query, which is often known as a “dirty read.”

How To Fix Error 605: Severity 21?

To fix this problem, restore data from the server backup file. If the backup file is missing, use the DBCC CHECKDB command to repair the data file. The following points will assist us in determining the root cause of this error:

• Examine all issues related to hardware and the system.
• Make sure that PAGE_VERIFY=CHECKSUM is enabled on SQL Server.
• If a SQL Server backup file is available, try to restore data.

How To Fix Error 605: Severity 12?

We should stick to the following the below points if the error severity is 12.

• Avoid using the reading uncommitted isolation level.
• During execution, do not make any changes to the current tables.
• Repeat the query until we no longer receive the error code.

[Looking for a solution to another query? We are just a click away.]

Conclusion

To conclude, we have seen the details of SQL Server Fatal Error 605 along with the methods from our Tech team to solve it.

If you are getting SQL error 605 while working with SQL Server then, this troubleshooting guide will help you resolve this issue.

User Scenarios:

Query-1 “I am acquiring following error while running queries from one of my tables:

In order to resolve this, someone told me to run DBCC CHECKDB command. I ran the check however, it reported 0 allocation errors along with 0 consistency errors hence, I am not sure where to go from here. Please help!”

Query-2“Code, which was working fine till last week is suddenly throwing this exception:

We are using .NET Framework 4.0 on the SQL Server 2008 R2 and this exception is thrown while running the stored procedure. I have no idea that how to fix this issue. Is there any solution for this?”

SQL Server Fatal Error 605 – Problem Description

Sometimes while users try to login with a user who is not a System Administrator user then, they encounter this Microsoft SQL Error 605 message. When the severity is Level 21, the session of a user becomes disconnected. After that, the error is written into SQL ERRORLOG and the Windows Application Event Log as EventID=605. The severity also can be 12 that will just throw the error as result to the client application.

In this error message, the first allocation unit that is after the “belongs to…” is the real allocation unit. The other allocation unit that is after “not to” is the expected allocation unit.

Note: An allocation unit is an ID that determines that from which index and object a page belongs to.

SQL Warning Fatal Error 605 occurred – Find the Cause

1. Error: 605, severity: 21, state: 3.
This error message can create a serious issue with the database page or with SQL Server engine while detecting the expected allocation.

2. Error: 605, severity: 12, state: 3.
The severity of this error message also can be Level 12 if the execution of query becomes failed while using the read uncommitted isolation level. Or, the NOLOCK query executed that is also called “dirty read”.

Microsoft SQL Error 605 – Find the Solution

The manual methods to resolve the SQL error 605 depends upon the reason behind its causes. Hence, recognize the cause and opt the fix accordingly.

1. If the Error is Occurred Due to Severity 21

The page may be damaged or incorrect if you encounter the error code 2533 after running DBCC CHECKDB or the CHECKTABLE command. Hence, to resolve this issue, one needs to restore data from the server backup file. In case if the backup file in not present then, utilize DBCC CHECKDB command to repair the data file. Below points will help you to determine the actual cause of this error:

• Analyze all the issues that are associated with hardware and system
• Make sure to enable PAGE_VERIFY=CHECKSUM on SQL Server
• If the backup file of SQL Server is available then, try to restore data

In case if the issue occurs even after executing DBCC CHECKDB command then, contact with the Microsoft technical support team for further help.

2. If the Error is Occurred Due to Severity 12

• Try not to use the reading uncommitted isolation level
• Do not modify the existing tables during execution
• Repeat query until you did not get the error code

MS SQL Error 605 – Resolve it by Expert Solution

SQL Database Recovery tool is an ultimate solution to repair corrupt MDF and NDF SQL Server Database and export it into SQL Server. The software is capable enough to recover the deleted SQL database table’s data. Using this, one can even scan and recover Triggers, Functions, Rules, Tables, Stored Procedures. It provides support to both ASCII and Unicode XML datatype and maintains the folder hierarchy throughout the process.

Final Words

This article includes a complete information about SQL error 605. Here, we have discussed different manual solutions to resolve this SQL Server fatal error 605 . In case, if the manual tricks do not work for you then, an alternate solution is also recommended here.