Article ID: 258084 - View products that this article applies to.
This article was previously published under Q258084
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 18977 (SQLBUG_65)
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.
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.
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:
197177If 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:
(https://support.microsoft.com/kb/197177/EN-US/ )INF: How to Obtain SQL Server 6.5 Service Pack 5a
274036For more information, contact your primary support provider.
(https://support.microsoft.com/kb/274036/EN-US/ )INF: How to Obtain SQL Server 6.5 Post Service Pack 5a Update
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.