SQL Server Performance Forum – Threads Archive

605 errors in tempdb

I have recently begun to see alot of 605 errors in tempdb, I have checked all of my hardware on both nodes of the cluster server, and the external storage array. Does anyone know why tempdb would keep becoming corrupt? I have scanned for a virus and run checkdb on all of the databases under the SQL server. This is becoming a big neusance, and I need to resolve it, can anyone help? Thanks in advance for the help!!

From BOL: Error 605
Severity Level 21
Message Text
Attempt to fetch logical page %S_PGID in database ‘%.*ls’ belongs to object ‘%.*ls’, not to object ‘%.*ls’. Explanation
This error occurs when MicrosoftÂ® SQL Serverâ„¢ detects database corruption. The second object specified in the text not to object ‘%.*ls’ is probably corrupt. Because this error can mask the existence of other errors, execute DBCC CHECKDB to determine the extent of the damage. If DBCC CHECKDB does not report additional errors, the first object mentioned is not corrupt. SQL Server detects database corruption when it traverses the pages 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 a damaged page chain, a corrupt Index Allocation Map (IAM), or an invalid entry in the sysobjects system table for that object. A clustered table has one doubly-linked page chain for the table data as well as one for each index level. A nonclustered index has a page chain for each level of the index. Pages in a heap are not linked. The IAM is used to find the pages of a heap. Although error 605 usually 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 the cache without the damage ever being written to the 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 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. Nontransient 605 errors are often caused by hardware or disk device driver failure. Action
Execute DBCC CHECKTABLE on the second object specified in the error message. To determine the full extent of the corruption, execute DBCC CHECKDB as soon as possible. Also check the error log for other errors, which often accompany a 605 error. If the 605 error is not transient, the problem is severe and you must run DBCC CHECKDB with one of the repair clauses. If the error involves an index page, use the REPAIR_REBUILD clause. If the error involves a data page, it may be necessary to use the REPAIR_ALLOW_DATA_LOSS clause. In the likely event that you cannot allow the loss of data, you will need to restore from a known clean backup. If the problem persists, contact your primary support provider. Have the output from DBCC CHECKDB available for review. Important If running DBCC CHECKDB with one of the repair clauses does not correct the index problem, or if you are unsure what effect DBCC CHECKDB with a repair clause has on your data, contact your primary support provider.
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
DBCC CHECKDB offers the REPAIR_REBUILD and REPAIR_ALLOW_DATA_LOSS clauses. The REPAIR_REBUILD clause rebuilds corrupt indexes and the REPAIR_ALLOW_DATA_LOSS clause fixes allocation problems. Sometimes, deleting pages is the only way to fix allocation problems. Typically, these pages contain data that was already deleted, but the pages may contain valid data. Therefore, deleting pages is a more risky option than using DBCC CHECKDB with a repair clause. Using DBCC CHECKDB with a repair clause fixes database corruption when a database backup is not available. If your database is a data warehouse, you may be able to continue operating without the lost data for some time before reloading the missing data. In these cases, use DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS clause to fix the damaged database. You can prevent problems by following these guidelines: Run SQL Server only on hardware and controllers that are certified for your operating system.
Perform regular backups in conjunction with DBCC CHECKDB statements. DBCC CHECKDB performs all checks that DBCC NEWALLOC and DBCC CHECKALLOC previously did, but DBCC CHECKDB is faster. This is the only way to be confident of the state of the database at the time of the backup.
If the data is critical, back up the transaction log frequently. This makes it possible to reduce your window of vulnerability, even in the event of a catastrophic hardware problem, to an hour or less.
In the most critical situations, use a standby server and a continually running batch job to take transaction backups off of the primary computer and continually restore them on the standby computer.
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 specifically platform-related.
Luis Martin
Moderator
SQL-Server-Performance.com

Thank you for the lengthy reply, I have gone back through the logs and determined that the first object refrenced in the 605 error is object ID 0. Going by your post, you say that this is probably caused by an unallocated page. How do I fix this issue? I am very new to SQL 2000, I mainly focus my attention on Exchange but this was a big issue that my boss asked me to look at. Thanks agian for all your help.

Try DBCC CHECKDB with REPAIR_REBUILD clause. And see if error still there. Luis Martin
Moderator
SQL-Server-Performance.com

Just a stupid reply…. if nothing helps, this problem should go when you reboot the server next time as TempDB rebuilds itself when the server reboots. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

True (not a stupid one doh’) and restarting SQL services should do the job, but check on the hardware issues that may contribute the trouble. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>