FIX: SPID May Stop Responding with CPU Spin During Log Out When it Drops Temp Procedure (258084)

SYMPTOMS

A system process ID (SPID) may stop responding during the process of dropping temporary stored procedures when the server is logging out a user. The procedures are being dropped by the server as designed because they were not explicitly dropped by the user before disconnecting the session.

A SPID in this state may appear in the sysprocesses system table as RUNNABLE, AWAITING COMMAND, and blocks other users who need access to the sysprocedures table in the tempdb system database. The thread associated with this SPID is in a CPU spin that typically consumes 99-100% of the processor.

WORKAROUND

Reduce or eliminate the usage of temporary stored procedures. You may also reduce or eliminate this problem if you explicitly drop the temporary stored procedures before the user disconnects.

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

A signature of this problem are the locks that the SPID is holding blocking other users. The blocking lock is an EX_INT lock on sysprocedures in the tempdb system database. An example sp_lock for a SPID in this state might look like this:

22     Ex_intent-blk                       5           0           tempdb
22     Ex_page                             5           1539        tempdb
22     Ex_page                             5           6893        tempdb
22     Ex_page                             5           6894        tempdb
22     Ex_page                             5           6895        tempdb
22     Ex_page                             5           6896        tempdb
22     Ex_page                             5           6897        tempdb
22     Ex_page                             5           6898        tempdb
22     Ex_page                             5           6899        tempdb
22     Ex_page                             5           6900        tempdb
22     Update_page                         5           6893        tempdb
22     Update_page                         5           6894        tempdb

The SPID that is not responding typically has multiple EX_PAGE and UP_PAGE locks on data pages within the sysprocedures system table as well as an EX_INT lock on the sysprocedures system table. This SPID cannot be killed because it is not responding during the process of logging out from the server.