TechNews

Methods to Fix SQL Server Error 605

5 years ago
5 years ago
233 Views

This SQL error 605 is an encounter when the MDF files are damaged or due to the inaccessible form of the SQL database. Although, there are some more reasons for this and they are unknown. To fix this error, the user must have good technical knowledge of SQL server. And sometimes the technical expertise is needed. So, without wasting time let us know about the known cause for SQL server error 605.

Messages that you’ll encounter in SQL 605 Error:

“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.”

Why SQL Server Error 605 Occurs?

If we study this error then we find two types of severity in this error. These are:

Severity 21: In Severity 12, the database page or the SQL server creates the problem while learning the actual value of expected allocation.

Severity 12: In Severity 12, the problem occurs when the server query uses the read uncommitted isolation level or NOLOCK query hint.

SQL Error 605 appears when a user logs into multiple systems, or a system other than Administrator access. The error message appears in RED color.

In case, you’ll get a severity 21, the previous session of the user terminated. Also, your error is registered in the SQL error log and on the Windows Application Event Log as Event ID 605. Hence this the possible reason that you will get an error 605.

Otherwise, in case of severity 12, in this situation, the query flops and starts utilizing read uncommitted isolation level.

Methods to Resolve error 605 in SQL Server

Firstly, it is a request to all of you that you have taken the backups of your data to neglect situations like data loss or data corruption.

When You Have Severity level 21

Firstly, you will have the correct information regarding your data file and page number given in the error message. After that, use the DBCC PAGE command:

dbcc traceon(3604)

dbcc page(‘MyDatabaseName’,1, 224,3)

dbcc traceoff(3604)

NOTE: You are independent to use any from the 0,1,2,3 choices as your last parameter (bold 3) of the DBCC PAGE command. You have two different commands & options to follow:

  1. From a complete Database backup, restore your page.
  2. Otherwise, Try these DBCC commands.

If we take experts’ opinions, then they recommend taking the help of the 1st method. But if you don’t have a copy of your backup data, then you move to the second option. Given below are some commands follow then to restore a page from the last database backup:

RESTORE DATABASE MyDatabaseName

PAGE = ‘1:224’

FROM DISK = ‘D:\MyDatabaseName.bak’

WITH NORECOVERY

Follow the alternate option, if you don’t have the required backup. The alternate option to use is the DBCC CHECKDB command.

When the user doesn’t have its backup copy then they are required to verify their severity level i.e. 21 or 12.

DBCC CHECKDB (‘MyDatabaseName’) WITH NO_INFOMSGS

Execute the above command to use the REPAIR_REBUILD.

Note: If there is a repair level REPAIR_ALLOW_DATA_LOSS, then this shows that you lose some of your data. Now, you have to follow the below commands:

  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

When You Have Severity level 12

When you’ll get severity level 12, you have to follow the below steps:

  1. Firstly, ignore the uncommitted isolation level.
  2. Also, while executing the SQL query don’t make changes.
  3. Be prepared to avoid unnecessary interruptions.

The steps that we show above help you to get your SQL Server Error 605. But we don’t guarantee these methods as they sometimes create an error itself on the users’ database.

Well! Not to worry. Of course! We have a much better plan for you to fix this complication and error 605. To know this, keep reading.

Well in the case of Manual methods, you’ll probably get an error, as they are not compatible with all versions of MS SQL server like 2014/2012/2008 R2/2008/2005. So, take the help of a professional SQL Recovery Tool to fix this issue and you will not find this error again. But these tools help you to recover the corrupted MDF files and all its components like Trigger, Rules, Functions, Tables, etc.

Wrap Up

In this technical guide, we’ll give you a complete guideline to fix SQL Server Error 605 with the help of all possible methods. You can use the provided commands to fix this issue, but it is better that you’ll take the help of professional tools to get yourself out of any kind of data loss or corruption. If you find any problem in any part, feel free to ask.

Shares: