KILL (Transact-SQL)

session ID

Is the session ID of the process to terminate. session ID is a unique integer (int) that is assigned to each user connection when the connection is made. The session ID value is tied to the connection for the duration of the connection. When the connection ends, the integer value is released and can be reassigned to a new connection.

Use KILL session ID to terminate regular nondistributed and distributed transactions that are associated with a specified session ID.

UOW

Identifies the Unit of Work ID (UOW) of distributed transactions. UOW is a GUID that may be obtained from the request_owner_guid column of the sys.dm_tran_locks dynamic management view. UOW also can be obtained from the error log or through the MS DTC monitor. For more information about monitoring distributed transactions, see the MS DTC documentation.

Use KILL UOW to terminate orphaned distributed transactions. These transactions are not associated with any real session ID, but instead are associated artificially with session ID = '-2'. This session ID makes it easier to identify orphaned transactions by querying the session ID column in sys.dm_tran_locks, sys.dm_exec_sessions, or sys.dm_exec_requests dynamic management views.

WITH STATUSONLY

Generates a progress report about a specified session ID or UOW that is being rolled back due to an earlier KILL statement. KILL WITH STATUSONLY does not terminate or roll back the session ID or UOW; the command only displays the current progress of the rollback.

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 kill include the following:

• AWAITING COMMAND

• CHECKPOINT SLEEP

• LAZY WRITER

• LOCK MONITOR

• SIGNAL HANDLER

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 Use Marked Transactions to Recover Related Databases Consistently (Full Recovery Model).

Using WITH STATUSONLY

Requires the ALTER ANY CONNECTION permission. ALTER ANY CONNECTION is included with membership in the sysadmin or processadmin fixed server roles.

A. Using KILL to terminate a session

KILL 53;
GO

B. Using KILL session ID WITH STATUSONLY to obtain a progress report

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

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

Reference