Terminates a user process that is based on the session ID (SPID in SQL Server 2000 and earlier) or unit of work (UOW). If the specified session ID or UOW has a lot of work to undo, the KILL statement may take some time to complete, particularly when it involves rolling back a long transaction.
In SQL Server 2000 and later versions, KILL can be used to terminate a normal connection, which internally terminates the transactions that are associated with the specified session ID. The statement can also be used to terminate orphaned and in-doubt distributed transactions when Microsoft Distributed Transaction Coordinator (MS DTC) is in use.
KILL is commonly used to terminate a process that is blocking other important processes with locks, or 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 you should not to kill include the following:
Use @@SPID to display the session ID value for the current session.
To obtain a report of active session ID values, you can query the session_id column of the sys.dm_tran_locks, sys.dm_exec_sessions, and sys.dm_exec_requests dynamic management views. You can also view the SPID column that is returned by the sp_who system stored procedure. If a rollback is in progress for a specific SPID, the cmd column in the sp_who result set for that SPID will indicate KILLED/ROLLBACK.
When a particular connection has a lock on a database resource and blocks the progress of another connection, the session ID of the blocking connection shows up in the blocking_session_id column of sys.dm_exec_requests or the blk column returned by sp_who.
The KILL command can be used to resolve in-doubt distributed transactions. These transactions are unresolved distributed transactions that occur because of unplanned restarts of the database server or MS DTC coordinator. For more information about in-doubt transactions, see the "Two-Phase Commit" section in Using Marked Transactions (Full Recovery Model).
Using WITH STATUSONLY
KILL WITH STATUSONLY generates a report only if the session ID or UOW is currently being rolled back because of a previous KILL session ID|UOW statement. The progress report states the amount of rollback completed (in percent) and the estimated length of time left (in seconds), in the following form:
Spid|UOW <xxx>: Transaction rollback in progress. Estimated rollback completion: <yy>% Estimated time left: <zz> seconds
If the rollback of the session ID or UOW has finished when the KILL session ID|UOW WITH STATUSONLY statement is executed, or if no session ID or UOW is being rolled back, KILL session ID|UOW WITH STATUSONLY will return the following error:
"Msg 6120, Level 16, State 1, Line 1"
"Status report cannot be obtained. Rollback operation for Process ID <session ID> is not in progress."
The same status report can be obtained by repeating the same KILL session ID|UOW statement without using the WITH STATUSONLY option; however, we do not recommend doing this. Repeating a KILL session ID statement might terminate a new process if the rollback had finished and the session ID was reassigned to a new task before the new KILL statement is run. Specifying WITH STATUSONLY prevents this from happening.
A. Using KILL to terminate a session
The following example shows how to terminate session ID 53.
KILL 53; GO
B. Using KILL session ID WITH STATUSONLY to obtain a progress report
The following example generates a status of the rollback process for the specific session ID.
KILL 54; KILL 54 WITH STATUSONLY; GO --This is the progress report. spid 54: Transaction rollback in progress. Estimated rollback completion: 80% Estimated time left: 10 seconds.
C. Using KILL to terminate an orphaned distributed transaction
The following example shows how to terminate an orphaned distributed transaction (session ID = -2) with a UOW of D5499C66-E398-45CA-BF7E-DC9C194B48CF.