Sql error 605

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.

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

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

MSSQLSERVER_605

Applies to: SQL Server (all supported versions)

Details

Explanation

This error generally signifies page or allocation corruption in the specified database. SQL Server detects corruption when reading pages belonging to a table either by following the page linkages or by using the Index Allocation Map (IAM). All pages allocated to a table must belong to one of the allocation units associated with the table. If the allocation unit ID contained in the page header does not match an allocation unit ID associated with the table, this exception is raised. The first allocation unit ID listed in the error message is the ID present in the page header, and the second allocation unit value is the ID associated with the table.

Data Corruption Errors

A severity level of 21 indicates potential data corruption. Possible causes are a damaged page chain, a corrupt IAM, or an invalid entry in the sys.objects catalog view for that object. These errors are often caused by hardware or disk device driver failure.

Transient Errors

A severity level of 12 indicates a potential transient error; that is, it occurs in the cache and does not indicate damage to data on disk. Transient 605 errors can be caused by the following conditions:

The operating system prematurely notifies SQL Server that an I/O operation has completed; the error message is displayed even though no actual data corruption exists.

Running a query with the Optimizer hint NOLOCK or setting the transaction isolation level to READ UNCOMMITTED. When a query that is using NOLOCK or READ UNCOMMITTED transaction isolation level tries to read data that is being moved or changed by another user, a 605 error occurs. To verify that it is a transient 605 error, rerun the query later.

In general, if the error occurs during data access but subsequent DBCC CHECKDB operations complete without error, the 605 error was probably transient.

User Action

If the 605 error is not transient, the problem is severe and must be corrected by performing the following tasks:

Identify the tables associated with the allocation units specified in the message by running the following query. Replace allocation_unit_id with the allocation units specified in the error message.

Execute DBCC CHECKTABLE without a REPAIR clause on the table associated with the second allocation unit ID specified in the error message.

Execute DBCC CHECKDB without a REPAIR clause as soon as possible to determine the full extent of the corruption in the entire database.

Check the error log for other errors that often accompany a 605 error and examine the Windows Event Log for any system or hardware related issues. Fix any hardware-related problems that are contained in the logs.

If the problem is not hardware related, perform one of the following tasks:

Restore the database from a known clean backup. You can leverage the page restore backup feature to restore just the damaged pages.

Run DBCC CHECKDB with the REPAIR clause recommended by the DBCC CHECKDB operation performed in step 3 to repair the corruption. If running DBCC CHECKDB with one of the REPAIR clauses does not correct the problem, contact your primary support provider. Have the output from DBCC CHECKDB available for review.

If you are not sure what effect DBCC CHECKDB with a REPAIR clause has on your data, contact your primary support provider before running this statement.

Источник

KB960770 — FIX: You receive error 605 and error 824 when you run a query that inserts data into a temporary table in SQL Server

Bug: #50003826 (SQL Hotfix)

Microsoft distributes Microsoft SQL Server 2008 fixes as one downloadable file. Because the fixes are cumulative, each new release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release.

Symptoms

In SQL Server 2008, you run a query that inserts data into a temporary table. The insert statement contains a subquery that references the same temporary table. When you run the query, you may receive an error message that resembles the following:

Msg 605, Level 21, State 3, Line 1

Attempt to fetch logical page (1:225) in database 2 failed. It belongs to allocation unit 281474980315136 not to 504403158513025024.

If you run the query again, you receive an error message that resembles the following:

Msg 824, Level 24, State 2, Line 1

SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x50758180; actual: 0x15658bfc). It occurred during a read of page (1:336) in database ID 2 at offset 0x000000002a0000 in file ‘C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATAtempdb.mdf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

In some cases, you may also receive an error message that resembles the following:

Msg 601, Level 12, State 3, Procedure p rocedure name, Line line number
Could not continue scan with NOLOCK due to data movement.

A possible query construct that can produce these errors is as follows:

Resolution

The fix for this issue was first released in Cumulative Update 3. For more information about how to obtain this cumulative update package for SQL Server 2008, click the following article number to view the article in the Microsoft Knowledge Base:

960484 Cumulative update package 3 for SQL Server 2008Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

956909 The SQL Server 2008 builds that were released after SQL Server 2008 was releasedAfter you install this cumulative update package, you have to enable trace flag 4135. To do this, you can add the -T4135 startup parameter. Or, you can use the dbcc traceon(4135) statement for a specific session.

Workaround

To work around this issue, add a column that has both a clustered primary key and an identity property to the temporary table. For example, run the following statement to change the temporary table:

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the «Applies to» section.

More Information

Although you encounter error message 824 or 605, the database does not become corrupted. Also, these error messages reference pages from the tempdb database.

For more information about what files are changed, and for information about any prerequisites to apply the cumulative update package that contains the hotfix that is described in this Microsoft Knowledge Base article, click the following article number to view the article in the Microsoft Knowledge Base:

960484 Cumulative update package 3 for SQL Server 2008

SQL Server 2008 R2 BPA information

The SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA) provides a rule to detect situations in which you do not have the cumulative update or the trace flag enabled to address this issue. The SQL Server 2008 R2 BPA supports both SQL Server 2008 and SQL Server 2008 R2.

If you run the BPA tool and encounter a «Database Engine — tempdb errors fix or trace flag missing» warning, you have to check the version of SQL Server and the trace flags that are configured to enable this fix.

References

SQL Server 2008 R2 BPA Rule

tempdb errors fix or trace flag missing

Note You can enable trace flag 4135 or trace flag 4199 to enable this fix. Trace flag 4135 was introduced in Cumulative Update package 3 for SQL Server 2008. Trace flag 4135 is also available in SQL Server 2008 Service Pack 1, in SQL Server 2008 Service Pack 2, and in SQL Server 2008 R2. Trace flag 4199 was introduced in Cumulative Update package 7 for SQL Server 2008, in Cumulative Update package 7 for SQL Server 2008 Service Pack 1, and in Cumulative Update package 1 for SQL Server 2008 R2. For more information about trace flag 4199, click the following article number to view the article in the Microsoft Knowledge Base:

974006 Trace flag 4199 is added to control multiple query optimizer changes previously made under multiple trace flags Because the fix for this problem involves a combination of a specific hotfix build and a trace flag to enable the fix, we are including the following table to show the different scenarios and the recommended action for you to take for each scenario.

For more information about the latest SQL Server builds, click the following article number to view the article in the Microsoft Knowledge Base:

957826 Where to find information about the latest SQL Server builds

References

For more information about the list of builds that are available after the release of SQL Server 2008, click the following article number to view the article in the Microsoft Knowledge Base:

956909 The SQL Server 2008 builds that were released after SQL Server 2008 was released

For more information about the Incremental Servicing Model for SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:

935897 An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems

For more information about the naming schema for SQL Server updates, click the following article number to view the article in the Microsoft Knowledge Base:

822499New naming schema for Microsoft SQL Server software update packagesFor more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base:

824684 Description of the standard terminology that is used to describe Microsoft software updates

Источник

MSSQLSERVER_605

Применимо к: SQL Server (все поддерживаемые версии)

Сведения

Объяснение

Обычно эта ошибка свидетельствует о повреждении страниц или об ошибке при их выделении в указанной базе данных. SQL Server выявляет это при чтении страниц таблицы либо по указателям, либо через карту распределения индекса (IAM). Все относящиеся к таблице страницы должны принадлежать одной из единиц распределения, связанных с данной таблицей. Если идентификатор единицы распределения, содержащийся в заголовке страницы, не соответствует идентификатору единицы распределения, связанной с таблицей, возникает это исключение. Первый идентификатор единицы распределения, приведенный в сообщении об ошибке, содержится в заголовке страницы, а второй является идентификатором, связанным с таблицей.

Ошибки повреждения данных

Уровень серьезности 21 указывает на потенциальное повреждение данных. Его причиной может стать повреждение цепочки страниц, карты распределения индекса или неверная запись в представлении каталога sys.objects для этого объекта. Зачастую такие ошибки вызваны сбоем оборудования или драйвера дискового устройства.

Нерегулярные ошибки

Уровень серьезности 12 означает потенциальную нерегулярную ошибку. Такие ошибки возникают в кэше и не указывают на повреждение данных на диске. Нерегулярные ошибки 605 могут быть вызваны следующими причинами.

Операционная система преждевременно извещает SQL Server о завершении операции ввода-вывода. В таком случае будет выдано сообщение об ошибке, хотя реальное повреждение данных места не имело.

Запуск запроса с указанием оптимизатору NOLOCK или задание уровня изоляции транзакций READ UNCOMMITTED. Когда запрос с NOLOCK или уровнем изоляции READ UNCOMMITTED пытается прочитать данные, перемещаемые или изменяемые другим пользователем, возникает ошибка 605. Чтобы убедиться, что имеет место нерегулярная ошибка 605, перезапустите запрос позже.

Как правило, если эта ошибка возникла при доступе к данным, но последующие операции DBCC CHECKDB завершились успешно, то скорее всего ошибка 605 была нерегулярной.

Действие пользователя

Если ошибка 605 появляется регулярно, это означает, что возникла серьезная проблема, которая должна быть решена путем выполнения следующих задач.

Выясните, какие таблицы связаны с указанными в сообщении единицами распределения, запустив следующий запрос. Замените allocation_unit_id указанными в сообщении об ошибке единицами распределения.

Выполните инструкцию DBCC CHECKTABLE без предложения REPAIR для таблицы, связанной со вторым идентификатором единицы распределения, указанным в сообщении об ошибке.

Выполните инструкцию DBCC CHECKDB без предложения REPAIR как можно скорее, чтобы выявить весь поврежденный экстент в базе данных.

Проверьте в журнале ошибок наличие других ошибок, которые обычно сопровождают ошибку 605 и выясните, не указывают ли они на какие-либо проблемы, связанные с системой или оборудованием. Исправьте все неполадки оборудования, обнаруженные в журналах.

Если проблема не связана с оборудованием, выполните одну из следующих задач.

Восстановите базу данных из безошибочной резервной копии. Восстановление страниц позволяет эффективно восстанавливать из резервных копий только поврежденные страницы.

Для исправления повреждения запустите инструкцию DBCC CHECKDB с предложением REPAIR согласно рекомендации операции DBCC CHECKDB, выполненной на шаге 3. Если в результате выполнения инструкции DBCC CHECKDB с одним из предложений REPAIR неполадка устранена не была, обратитесь к вашему основному поставщику услуг технической поддержки. Подготовьте к просмотру выходные данные инструкции DBCC CHECKDB.

Если достоверно неизвестно, к каким последствиям приведет выполнение инструкции DBCC CHECKDB с выбранным предложением REPAIR, перед выполнением этой инструкции обратитесь к вашему основному поставщику услуг технической поддержки.

Источник

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.

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.

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.