KILL Statement

Terminates a user's process based on the system process ID.

Syntax

KILL spid

where

spid

Specifies the system process ID (spid) of the process you want to terminate. The spid is a unique integer (smallint) assigned to processes as they occur; no user is permanently assign a spid. When a process is active, it is stored in the sysprocesses system table.

In addition to storing the ID of the process, the sysprocesses table also stores other process information, such as status (runnable, waiting for a lock, sleeping, and so on), the suid (the server login ID of the user who executed the statement), program_name (the "host_name" of the application program ¾ if not explicitly assigned, usually returns the computer name), and so on.

Remarks

Because an spid is assigned only to active processes, you can get a report on the current processes by executing the sp_who system stored procedure.

This is a typical example:

spid   status     loginame hostname  blk   dbname     cmd              
------ ---------- -------- --------- ----- ---------- ---------------- 
1      sleeping   sa                 0     master     MIRROR HANDLER   
2      sleeping   sa                 0     master     LAZY WRITER      
3      sleeping   sa                 0     master     CHECKPOINT SLEEP 
4      sleeping   sa                 0     master     RA MANAGER       
10     sleeping   Mary     WKSTA1    0     pubs       AWAITING COMMAND 
11     runnable   Joe      WKSTA5    10    pubs       SELECT           

The blk column contains the system process ID of a blocking process; if there is one, this value will reference the spid column of this table (self-referencing). A blocking process is one that is holding resources that another process needs. In this example, process 11 (a SELECT on a table) is blocked by process 10 (an uncommitted transaction). Although well-designed transactions should complete quickly and rarely block others, a transaction that does block another transaction can be terminated with the KILL statement.

System processes (MIRROR HANDLER, LAZY WRITER, CHECKPOINT SLEEP, and RA MANAGER) and processes running an extended stored procedure cannot be terminated.

You can get a report on the current locks and each spid of the process holding them by using the sp_lock system stored procedure. The following information is returned when the SA executes sp_lock:

spid   locktype             table_id    page        dbname          
------ -------------------- ----------- ----------- --------------- 
15     Sh_intent            313052151   0           master          
10     Ex_table-blk         16003088    0           pubs            

In this example, process 10 has a blocking, exclusive table-lock. Process 15 (the process running sp_lock has a shared intent lock on sysprocesses (the table from which this output is retrieved).

Permission

KILL permission defaults to the system administrator and is not transferable.

Example

This example shows how to terminate the system process ID 10.

KILL 10

See Also