kill

Description

Kills a process.

Syntax

kill spid

Parameters

spid: is the identification number of the process you want to kill. spid must be a constant; it cannot be passed as a parameter to a stored procedure or used as a local variable. Use sp_who to see a list of processes and other information.

Examples

Example 1

kill 1378

Usage

To get a report on the current processes, execute sp_who. Following is a typical report:

fid spid  status    loginame origname hostname blk  dbname 
       cmd 
--- ----- --------   -------- -------- -------- --- ------ 
       --------------
  0    1  recv sleep  bird     bird     jazzy    0   master 
       AWAITING COMMAND
  0    2  sleeping    NULL     NULL              0   master 
       NETWORK HANDLER
  0    3  sleeping    NULL     NULL              0   master 
       MIRROR HANDLER
  0    4  sleeping    NULL     NULL              0   master 
       AUDIT PROCESS
  0    5  sleeping    NULL     NULL              0   master 
       CHECKPOINT SLEEP
  0    6  recv sleep  rose     rose     petal    0   master 
       AWAITING COMMAND
  0    7  running     robert   sa       helos    0   master 
       SELECT
  0    8  send sleep  daisy    daisy    chain    0   pubs2  
       SELECT
  0    9  alarm sleep lily     lily     pond     0   master 
       WAITFOR
  0   10  lock sleep  viola    viola    cello    7   pubs2  
       SELECT

The spid column contains the process identification numbers used in the Transact-SQL kill command. The blk column contains the process ID of a blocking process, if there is one. A blocking process (which may have an exclusive lock) is one that is holding resources that are needed by another process. In this example, process 10 (a select on a table) is blocked by process 7 (a begin transaction followed by an insert on the same table).

The status column reports the state of the command. The following table shows the status values and the effects of sp_who:

**Table 7-26: Status values reported by sp_who**
Status	Description	Effect of kill command
`recv sleep`	Waiting on a network read.	Immediate.
`send sleep`	Waiting on a network send.	Immediate.
`alarm sleep`	Waiting on an alarm, such as waitfor delay "10:00".	Immediate.
`lock sleep`	Waiting on a lock acquisition.	Immediate.
`sleeping`	Waiting on disk I/O or some other resource. Probably indicates a process that is running, but doing extensive disk I/O.	Process is killed when it “wakes up,” usually immediately. A few sleeping processes do not wake up, and require an Adaptive Server reboot to clear.
`runnable`	In the queue of runnable processes.	Immediate.
`running`	Actively running on one of the server engines.	Immediate.
`infected`	Adaptive Server has detected a serious error condition; extremely rare.	kill command not recommended. Adaptive Server restart probably required to clear process.
`background`	A process, such as a threshold procedure, run by Adaptive Server rather than by a user process.	Immediate; use kill with extreme care. Recommend a careful check of sysprocesses before killing a background process.
`log suspend`	Processes suspended by reaching the last-chance threshold on the log.	Immediate.

To get a report on the current locks and the spids of the processes holding them, use sp_lock.

Standards

SQL92 – Compliance level: Transact-SQL extension.

Permissions

kill permission defaults to System Administrators and is not transferable.