FIX: Server Unresponsive After Deadlocks Under Tempdb Stress (245807)

SYMPTOMS

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:

WARNING:Pss found with open sdes;pspid 15,psuid 1,pcurdb 2,system table entry 0,sdesp 0x25d0f88,objid 2

CAUSE

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.

STATUS

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:

197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a

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:

274036 INF: How to Obtain SQL Server 6.5 Post Service Pack 5a Update

For more information, contact your primary support provider.

MORE INFORMATION

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:

Your server command (process id %ld) was deadlocked with another process and  has been chosen as deadlock victim. Re-run your command.
Error while undoing log row in database '%.*s'.  Rid pageid is %S_RID. The SQL Server is terminating this process.

The preceding error is not reported to the process or in the SQL Server errorlog.