Presents your 
SQL SERVER E-NEWSLETTER for June 19, 2003

<------------------------------------------->

USE KILL TO TERMINATE PROBLEM CONNECTIONS

Rogue user processes can eat up your connections and dramatically limit
the number of connections available to SQL Server.

KILL is a command typically used to stop processes that are using locks
to block other processes or that are executing a query that's gobbling up
system resources. KILL stops a normal connection and, consequently,
rolls back its associated transactions.

KILL uses the SPID (system process ID) to identify the offending process
so you have to run sp_who to get a report on valid SPID values.

For example, to terminate process 53, you use the following code:

KILL 53

If the KILL command is used to stop a particularly long transaction, the
command may take some time as it rolls back the transaction. You can
continue to check on the rollback's progress by using the KILL command with
the WITH STATUSONLY argument. This will return a report that gives you
the percentage of the rollback completed and the estimated time left. 
For example:

--Kill the transaction
KILL 54
--Check its rollback status
KILL 54 WITH STATUSONLY

Your result will look like this:

spid 54: Transaction rollback in progress. Estimated rollback
completion: 80%
Estimated time left: 10 seconds.

If the rollback has already completed, you'll get the following error:

Status report cannot be obtained. KILL/ROLLBACK operator for Process
ID|UOW
<xxx> is not in progress.

You can also use the KILL command to stop "orphaned" distributed
transactions. When a distributed transaction isn't associated with any 
SPID, it's considered orphaned.

You can spot an orphaned process because sp_who will return a -2 for its
SPID. If you run sp_who and discover a -2 SPID, it means an orphaned
process has created a lock.

Your next step is to get the offending process's UOW (unit of work ID)
from the syslockinfo table, which is a table that provides the UOW for
every lock held by a DTC transaction. You can also get the UOW from the
error log or through a DTC monitor.

Here's an example of using KILL with the UOW:

KILL 'D5499C66-E398-45CA-BF7E-DC9C194B48CF'

One other note about KILL: You cannot use it to terminate a system
process or a process running an extended stored procedure.

J.E. Harvey, MCSD, MCDBA, has been tinkering, writing, and consulting
about technology for more than two decades.

----------------------------------------