Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
How to troubleshoot Msg 605 with SQL Server
Article ID: 2015739 - View products that this article applies to.
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
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:
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
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,
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).
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:
(http://go.microsoft.com/fwlink/?LinkId=151500)for other considerations.
Article ID: 2015739 - Last Review: April 1, 2010 - Revision: 1.0