Microsoft KB Archive/258084

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

Article ID: 258084

Article Last Modified on 9/6/2006

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q258084



BUG #: 18977 (SQLBUG_65)



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.

Keywords: kbbug kbfix kbqfe KB258084

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.