Methods to fix error Msg 605 with SQL server

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.

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

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.

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.