How to troubleshoot Msg 605 with SQL Server
A query can fail with an error like the following if it needs to read a database page where the allocation unit as stored in the page header (also referred to as the actual allocation unit) does not match the expected allocation unit based on table(s) referenced in the query:
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
When the severity is Level 21, the user session is disconnected and the error is written into the SQL ERRORLOG and Windows Application Event Log as EventID=605. The severity could also be 12 which would only result in the error returned to the client application. Read below in the remaining portions of the article for more information about the severity 12 scenario.
In this message the first allocation unit listed (after the "belongs to...") is the actual allocation unit and the second allocation unilt (the one after "not to") is the expected allocation unit. An allocation unit is an ID that identifies which index and object a page belongs to.
Severity = 21
This problem could be an issue with the database page or a problem with the SQL Server engine when determining what the expected allocation unit should be. See the Resolution section for more details.
Severity = 12
The severity of this error can also be Level 12 if the query fails because it is using read uncommitted isolation level or the NOLOCK query hint (also known as a "dirty read").
Severity = 21
If DBCC CHECKDB or CHECKTABLE reports an error (which should be Msg 2533), then the page is damaged or an incorrect page. You should restore from a backup to resolve the problem. If you cannot restore from a backup, use the repair options with DBCC CHECKDB. To find the cause of this problem you should use techniques as you would with other database corruption errors including:
- Check for hardware or system level problems (A tool such as SQLIOSIM can be helpful to do this. SQLIOSIM ships with SQL Server 2008 and SQL Server 2008 R2 and does not require a separate download)
- Ensure you have the PAGE_VERIFY=CHECKSUM database option turned on. While it is possible to still encounter a Msg 605 error with checksum enabled (Ex. "scribbler" problem or incorrect page provided from I/O system), enabling checksum can provide definitive proof that the page was modified incorrectly within the I/O system.
- Try to restore a database backup you know that will be "clean" (no errors from CHECKDB) and transaction log backups you know span the time when the error was encountered. If you can "replay" this problem by restoring a "clean" database backup and transaction logs then contact Microsoft Technical Support for assistance.
For more information about using CHECKDB and causes of database corruption problems see the article: How to troubleshoot database consistency errors reported by DBCC CHECKDB
If DBCC CHECKDB does not report an error and the problem occurs frequently, you should contact Microsoft Technical Support for assistance. Be prepared to help find the query that is encountering the Msg 605 error. The More Information section talks about how to identify the query that has encountered this error.
Severity = 12
- Avoid using a read uncommitted isolation level (or NOLOCK hint)
- Be prepared to retry the query until you do not see the error
- Avoid making changes to the affected table(s) while running the query.
- The More Information section describes how to find the query encountering this error in this state.
To find what table belongs to an allocation unit as listed in the message, use the following query in the context of the database as found from the database id in the message (substitute the <allocation_unit_id> for the one you want to find as listed in the message)
SELECT au.allocation_unit_id, OBJECT_NAME(p.object_id) AS table_name, fg.name AS filegroup_name,
au.type_desc AS allocation_type, au.data_pages, partition_number
FROM sys.allocation_units AS au
JOIN sys.partitions AS p ON au.container_id = p.partition_id
JOIN sys.filegroups AS fg ON fg.data_space_id = au.data_space_id
WHERE au.allocation_unit_id = <allocation_unit_id>
ORDER BY au.allocation_unit_id
If the actual allocation unit is not found in the catalog views, the page may be damaged. If the actual allocation unit is found, it may be that the page is incorrect as read from disk (for example, the I/O system returns back the wrong page from disk).
The only repair option for this error is repair_allow_data_loss. The page will be deallocated and all data on this page will be lost. When you use repair, you will see a sequence of errors and message like the following:
Repair: The page (1:224) has been deallocated from object ID 69575286, index ID 0, partition ID 72057594039238656, alloc unit ID 72057594040287232 (type In-row data).
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:224) allocated to object ID 69575286, index ID 0, partition ID 72057594039238656, alloc unit ID 72057594040287232 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
The error has been repaired.
The SQL Server documentation includes similar information about this error as found at: MSSQLSERVER_605
To find out what query encountered this error, you can use the following techniques:
- For SQL Server 2008 and later versions, see if the system_health session has a record of the error which should include the query text. See the following resource for more information about the system_health session: http://blogs.msdn.com/psssql/archive/2008/07/15/supporting-sql-server-2008-the-system-health-session.aspx
- Use SQL Server Profiler and capture the SQL:BatchStarting, RPC:Starting, and Exception Events. Find the query that precedes the Exception Event for 605 for the session associated with the Exception Event.
Article ID: 2015739 - Last Review: 05/07/2014 07:20:00 - Revision: 2.0