Saturday, December 14, 2013

SQL - Attempt to fetch logical page in database failed. It belongs to allocation unit

Generally this error message means your database structure is corrupted, and there is nothing you can do.
you cannot drop the affected tables because they cannot be accessed.
The only real solution to this problem is to export all of the data you can get your hands on into a proper working database.

Moral of the story, backup properly. I don't even know why they bother to have databases that don't backup. 


Solution suggested from http://msdn.microsoft.com/en-us/library/aa337419.aspx

  1. Identify the tables associated with the allocation units specified in the message by running the following query. Replaceallocation_unit_id with the allocation units specified in the error message.
    USE database_name;
    GO
    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 OR au.allocation_unit_id = allocation_unit_id
    ORDER BY au.allocation_unit_id;
    GO
  2. Execute DBCC CHECKTABLE without a REPAIR clause on the table associated with the second allocation unit ID specified in the error message. (Table in the table_name field. add sys. in front of the name if it is a system table)
  3. Execute DBCC CHECKDB without a REPAIR clause as soon as possible to determine the full extent of the corruption in the entire database. (dbcc checkdb ('mydb',REPAIR_ALLOW_DATA_LOSS))
  4. 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.

No comments:

Post a Comment