kill

Kills a process.

Syntax

kill spid [with (statusonly | force)]

Parameters

Examples

Usage

  • Execute sp_who to get a report on the current processes, such as:
    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 status values and the effects of sp_who are:

    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 SAP ASE restart to clear.

    runnable

    In the queue of runnable processes.

    Immediate.

    running

    Actively running on one of the server engines.

    Immediate.

    infected

    The SAP ASE server has detected a serious error condition; extremely rare.

    kill command not recommended. A SAP ASE server restart is probably required to clear the process.

    background

    A process, such as a threshold procedure, run by an SAP ASE 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.

  • In a clustered environment, a privileged Kerberos user can use the kill command to stop the spid for a DBMS task on a remote instance.

  • SAP ASE issues this message if you use with force to terminate a spid that holds spinlocks:
    You cannot kill spid 'spid_number' with force option as it is holding spinlock(s).
  • spid must be a constant; it cannot be passed as a parameter to a stored procedure or used as a local variable.

See also sp_lock, sp_who in Reference Manual: Procedures.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

The permission checks for kill differ based on your granular permissions settings.

SettingDescription
Enabled

With granular permissions enabled, you must have the kill privilege to kill you own process, and have the kill any process privilege to kill another user's processes.

Disabled

With granular permissions disabled, you must be a user with sa_role.

kill privilege is not transferable.

Related reference
shutdown