How to Resolve error 605 in SQL Server??

Synopsis : Let’s see How to Resolve error 605 in SQL Server

You can encounter the following messages such as,

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

This error is encountered when there is damaged MDF files which are inaccessible from SQL database. However, the precise reason is unknown. To resolve error 605 in SQL Server, many times, you might need the professional expertise of a SQL server expert.

To know more about SQL Servers, You can read: MS SQL Server

Causes of SQL Server Error 605

There are two types of severity in this error,

Severity 21: In this, the problem could be with the pages of database or with engine of SQL server while learning the actual value of expected allocation.

Severity 12: This is caused when the server query uses read uncommitted isolation level or NOLOCK query hint.

Error Msg 605 appears when a user tries to log through the system other than the system administrator. The error appears in RED color.

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.So, this is how you know this error has occured.

You can experience severity level 12 only when it is returned from client application and also if the query flops in situation when it is utilizing read uncommitted isolation level.

Methods to Resolve error 605 in SQL Server

First and foremost, you should always keep the backup of your data to minimize loss of valuable data.

In the case of Severity level 21

You can follow the following steps.

First of all you need to have the right information about the data file and page number given in error message,then use the DBCC PAGE command.

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

NOTE: You could use any of the 0,1,2,3 options 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

According to experts, it is recommend to opt for the 1st option, but just in case you don’t have last full backup, the you could easily opt for a second option. Given below TSQL, follow the commands to restore the page from the last complete database backup

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

But, it can happen that you don’t have the required backup, then the best option is to use DBCC CHECKDB command.

If there is no backup of the data, then first you require to verify about the severity level i.e. 21 or 12.

DBCC CHECKDB (‘MyDatabaseName’) WITH NO_INFOMSGS

After executing the above command, it might be recommended to use REPAIR_REBUILD.

Note: If you see repair level REPAIR_ALLOW_DATA_LOSS, unfortunately you may lose some of your data. Follow the command below:

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

In case of severity level 12,

In case of severity level 12, you can follw the steps given below:

1. First of all,ignore uncommitted isolation level (or NOLOCK hint).
2. Do not make any changes while running the SQL query.
3. Try to avoid making unnecessary interruptions.

These steps may provide you with some relief from error 605 in sql server, but still, these steps do not guarantee high-end qualitative as there is a probability that the SQL server edition may not favor the retrieval process making your job tough. So, to resolve error 605 in SQL Server, you may need a more profesional solution.

There are many types of databases like MS SQL Server 2014/2012/2008 R2/2008/2005 can be used to store the MDF files. It might happen that theabove mentioned approach does not works well for SQL Server 2014, then it would become impossible to fetch and secure the data.

Therefore, it is necessary to make use of a tool that can guarantee the recovery of corrupt MDF files to resolve error 605 in SQL Server

Professional SQL Recovery to Resolve error 605 in SQL Server

If you want complete recovery of corrupt MDF file components like Triggers, Rules, Functions, Tables, and Stored Procedures, etc. If you wish to preview the recovered file components, then you are recommended to install the Database File Recovery SQL database recovery tool.

Important Features of SQL Database Recovery Tool

• Allows complete recovery of tables, views, stored procedure, programmability, triggers, default and functions.

• Complete MDF and NDF files.
• Restores data either in SQL Server database format or SQL Server compatible Script format.
• Allows SQL Server ROW-compression and PAGE-compression.
• Provides preview option and supports BLOB, image, XML and large page data.
• Compatible with Windows 10, 8, 7, Vista, 2003, XP and 2000
• Support SQL Server version: 2000, 2005, 2008, 2012, 2014 and 2016.

Conclusion

In this blog, I have tried to explain the methods to resolve error 605 in SQL Server with different severity levels. You can try running the above-explained commands for recovery, but it is not a hundred percent efficient option for recovery. So, you can use the professional tool to resolve error 605 in SQL Server

 

Related Post