FIX: Server Unresponsive After Deadlocks Under Tempdb Stress

Under high stress in tempdb and heavy deadlocking, the server appears unresponsive to some client queries involving tempdb database. You may also see some processes in tempdb database being blocked by spid "-1" (as seen in the "blocked" column) in sysprocesses. Some Access Violations (AV) may also be recorded in the SQL Server Errorlog, along with some warning messages like:

Deadlocks between multiple processes creating temporary tables, or indexes on temporary tables results in partial cleanup during rollback of the victim process. This results in access violations and sometimes the process is partially cleaned up, showing as spid "-1" in the "blocked" column in sysprocesses. This partially rolled back process may hold locks on system tables, thereby blocking other processes, resulting in what appears to be a system hang.

Microsoft has confirmed this to be a problem in SQL Server version 6.5. This problem has been corrected in the Post Service Pack 5a Update for Microsoft SQL Server version 6.5. To install the Post Service Pack 5a Update, you must have either SQL Server 6.5 SP5 or SP5a installed.

For information about how to download and install the SQL Server 6.5 Service Pack 5a, refer to the following article in the Microsoft Knowledge Base: If you already have SQL Server 6.5 SP5 or SP5a installed, you can download the Post SP5a Update from the following article in the Microsoft Knowledge Base: For more information, contact your primary support provider.

This problem occurs with SQL Server version 6.5 Service Pack 4 and later and does not occur in prior Service Packs. If you have Trace Flag 1204 turned ON, you can get the deadlock information in the errorlog and determine the process that was chosen as the victim. DBCC Outputbuffer on the victim process shows: The preceding error is not reported to the process or in the SQL Server errorlog.