Error 605

Severity Level 21

Message Text

Attempt to fetch logical page %ld in database '%.*s' belongs to object '%.*s', not to object '%.*s'.

Explanation

This fatal error occurs when SQL Server detects database corruption. The second object specified in the text not to object '%.*s' is probably corrupt. Because this error can mask the existence of other errors, you must run dbcc to determine the extent of the damage. If dbcc checkdb and dbcc checkalloc do not report additional errors, the first object mentioned is not corrupt.

SQL Server detects database corruption when it traverses the page chain of an object and finds a page in the chain whose object ID does not match that of the object being accessed. There is probably either a damaged page chain or an invalid entry in the sysobjects system table for that object. There is one doubly-linked page chain for the table data as well as one for each index level.

Although error 605 normally displays two object names, other variations can occur:

• If instead of an object name the error displays a number greater than 0, it means that an attempt was made to reference an object ID that does not exist in a system table for that object.
• If the error reports the first object ID as 0, an unallocated page was probably encountered. (There is no object ID equal to 0.)
• If the error states that a page belongs to object ALLOCATION, some of the allocation structures used by the database might be corrupted.

Usually this error occurs after the corruption has been written to the database on disk, but it can also occur entirely in cache without the damage ever being written to disk. This is known as a transient 605 error and is not associated with data corruption. If error 605 occurs during data access, but subsequent dbcc checkdb and dbcc checkalloc statements complete without error, the 605 error was probably transient. Transient 605 errors can be caused by the operating system prematurely notifying SQL Server that an I/O operation has completed; the error message is displayed even though no actual data corruption exists.

Non-transient 605 errors are often caused by hardware or disk device driver failure.

Action

Run the dbcc checktable statement on the second object specified in the error message. To determine the full extent of the corruption, run the dbcc checkdb and dbcc checkalloc statements as soon as possible. Also check the error log for other errors, which often accompany a 605 error. For information about using DBCC, see the Microsoft SQL Server Transact-SQL Reference.

If the 605 error is not transient, the problem is severe and you will probably need to restore from a known clean backup. If the problem persists, contact your primary support provider. Have the output from the appropriate DBCC statements ready for review.

In addition, run hardware diagnostics and correct any problems. You might find it beneficial to perform a completely new setup on the computer, including reformatting the disk drives and reinstalling the operating system. This eliminates the possibility that a .DLL or .EXE program is corrupted. You can also examine your operating-system error log to see if the error occurred as the result of hardware failure.

Finally, be sure that your system does not have write caching enabled on the disk controller. If you suspect this to be the problem, contact your hardware vendor.

Additional Information

Recovering from a data corruption problem using any technique other than restoring the database from a known clean backup is unreliable at best. As a helpful analogy, consider the problem of cross-linked clusters that sometimes occurs in the MS-DOS FAT file system. Understanding the meaning and implications of this problem requires a comprehensive understanding of the FAT file system and its associated data structures. Under some circumstances, a proficient user might be able to use a disk editor to patch the disk structure; however, this would require a great investment in time and, in most cases, the results would be unreliable, especially if the data is not text.

Because SQL Server data structures are more complex than those associated with the MS-DOS FAT file system, they are even more difficult to patch. As recommended earlier, the best recovery for data corruption is to restore from known clean backups and to call your primary support provider if problems persist.

You can prevent problems by following these guidelines:

1. Run SQL Server only on hardware and controllers that are certified for your operating system.
2. Perform regular backups in conjunction with DBCC CHECKALLOC and DBCC CHECKDB. This is the only way to be confident of the state of the database at the time of the backup.
3. If the data is critical, frequently dump the transaction log. This makes it possible to reduce your window of vulnerability ¾ even in the event of a catastrophic hardware problem ¾ to an hour or less.
4. In the most critical situations, use a "hot standby" computer and a continually running batch job to take transaction dumps off of the primary computer and continually restore them on the standby computer.
5. If you have persistent data corruption problems, try to swap the computer, the controllers, and the disk device drivers for components of a different type. This makes it easier to determine whether the problem is platform-specific.