KILL
Terminates a user process based on the system process ID (SPID) or unit of work (UOW). If the specified SPID or UOW has a lot of work to undo, the KILL command may take some time to complete, particularly when it involves rolling back a long transaction.
In Microsoft® SQL Server™ 2000, KILL can be used to terminate a normal connection, which internally terminates the transactions associated with the given SPID. In addition, the command can also be used to terminate all orphaned distributed transactions when Microsoft Distributed Transaction Coordinator (MS DTC) is in use. A distributed transaction is orphaned when it is not associated with any current SPID.
Syntax
KILL {spid | UOW} [WITH STATUSONLY]
Arguments
spid
Is the system process ID (SPID) of the process to terminate. The SPID value is a unique integer (smallint) assigned to each user connection when the connection is made, but the assignment is not permanent.
Use KILL spid to terminate regular non-distributed and distributed transactions associated with a given SPID.
UOW
Identifies the Unit of Work ID (UOW) of the DTC transaction. UOW is a character string that may be obtained from the syslockinfo table, which gives the UOW for every lock held by a DTC transaction. UOW also may be obtained from the error log or through the DTC monitor. For more information on monitoring distributed transactions, see the MS DTC user manual.
Use KILL UOW to terminate orphaned DTC transactions, which are not associated with any real SPID and instead are associated artificially with SPID = '-2'. For more information on SPID = '-2', see the Remarks section later in this topic.
WITH STATUSONLY
Specifies that SQL Server generate a progress report on a given spid or UOW that is being rolled back. The KILL command with WITH STATUSONLY does not terminate or roll back the spid or UOW. It only displays the current progress report.
For the KILL command with WITH STATUSONLY option to generate a report successfully, the spid or UOW must be currently in the rollback status. The progress report states the amount of rollback completed (in percent) and the estimated length of time left (in seconds), in this form:
Spid|UOW <xxx>: Transaction rollback in progress. Estimated rollback completion: yy% Estimated time left: zz seconds.
If the rollback of the spid or UOW has completed when the KILL command with the WITH STATUSONLY option is executed, or if no spid or UOW is being rolled back, the KILL with WITH STATUSONLY will return the following error:
Status report cannot be obtained. KILL/ROLLBACK operator for Process ID|UOW <xxx> is not in progress.
The same status report can be obtained by executing twice the KILL spid|UOW command without the WITH STATUSONLY option; however, this is not recommended. The second execution of the command may terminate a new process that may have been assigned to the released SPID.
Remarks
KILL is commonly used to terminate a process that is blocking other important processes with locks, or to terminate a process that is executing a query that is using necessary system resources. System processes and processes running an extended stored procedure cannot be terminated.
Use KILL very carefully, especially when critical processes are running. You cannot kill your own process. Other processes not to kill are:
- AWAITING COMMAND
- CHECKPOINT SLEEP
- LAZY WRITER
- LOCK MONITOR
- SELECT
- SIGNAL HANDLER
Execute sp_who to get a report on valid SPID values. If a rollback is in progress for a specific SPID, the cmd column for the specific the SPID in the sp_who result set will indicate 'KILLED/ROLLBACK'.
Use @@SPID to display the SPID value for the current session.
In SQL Server 2000, the KILL command can be used to resolve SPIDs associated with non-distributed and distributed transactions. KILL also can be used to resolve orphaned or in-doubt distributed transactions. A distributed transaction is orphaned when it is not associated with any current SPID.
The SPID value of '-2' is set aside as an indicator of connectionless, or orphaned, transactions. SQL Server assigns this value to all orphaned distributed transactions, making it easier to identify such transactions in sp_lock (spid column), sp_who (blk column), syslockinfo, and sysprocesses. This feature is useful when a particular connection has a lock on the database resource and is blocking the progress of a transaction. The user would be able to identify the SPID that owns the lock, and end the connection.
The KILL command can be used to resolve in-doubt transactions, which are unresolved distributed transactions resulting from unplanned restarts of the database server or DTC coordinator. For more information on resolving in-doubt transactions, see Troubleshooting DTC Transactions.
Permissions
KILL permissions default to the members of the sysadmin and processadmin fixed database roles, and are not transferable.
Examples
A. Use KILL to terminate a SPID
This example shows how to terminate SPID 53.
KILL 53
B. Use KILL spid WITH STATUSONLY to obtain a progress report.
This example generates a status of the rollback process for the specific spid.
KILL 54
KILL 54 WITH STATUSONLY
--This is the progress report.
spid 54: Transaction rollback in progress. Estimated rollback completion: 80% Estimated time left: 10 seconds.
C. Use KILL to terminate an orphan distributed transaction.
This example shows how to terminate an orphan (SPID = -2) transaction with UOW = D5499C66-E398-45CA-BF7E-DC9C194B48CF.
KILL 'D5499C66-E398-45CA-BF7E-DC9C194B48CF'
See Also
'
)
|
|