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:
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.
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:
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.