Killing Processes

A process is a unit of execution carried out by SAP ASE. The kill command removes ongoing processes.

Each process is assigned a unique process identification number when it starts. This number is called a spid. These numbers are stored, along with other information about each process, in master..sysprocesses. Processes running in a parallel-processes environment create child processes, each of which has its own spids. Several processes create and assign spids: starting SAP ASE, login tasks, checkpoints, the housekeeper tasks, and so on. You can see most of the information by running sp_who.

Running sp_who on a single-engine server shows the sp_who process running and all other processes that are “runnable” or in one of the sleep states. In multi-engine servers, there can be a process running for each engine.

The most frequent reason for killing a process is that it interferes with other users, and the person responsible for running it is not available. The process may hold locks that block access to database objects, or there may be many sleeping processes occupying the available user connections. A system administrator can kill most running or "runnable" processes, including those that are waiting for:

An alarm, such as a waitfor command
Network sends or receives
A lock
Synchronization messages from another process in a family

SAP ASE allows you to kill processes only if it can cleanly roll back any uncompleted transactions and release all system resources that are used by the process. For processes that are part of a family, killing any of the child processes also kills all other processes in the family. However, it is easiest to kill the parent process. For a family of processes, the kill command is detected more quickly if the status of the child processes is sync sleep.

This table shows the status values that sp_who reports and when the kill command takes effect:

Status	Indicates	Effects 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.
`sync sleep`	Waiting on a synchronization message from another process in the family.	Immediate. Other processes in the family must also be brought to state in which they can be killed.
`sleeping`	Waiting on a disk I/O, or some other resource. Probably indicates a process that is running, but doing extensive disk I/O	Killed when it “wakes up,” usually immediate; a few sleeping processes do not wake up and require a server restart to clear.
`runnable`	In the queue of runnable processes.	Immediate.
`running`	Actively running on one of the server engines.	Immediate.
`infected`	Server has detected serious error condition; extremely rare.	kill command not recommended. Server restart probably required to clear process.
`background`	A process, such as a threshold procedure, run by SAP ASE 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.

Only system administrators can issue the kill command; permission to use it cannot be transferred.

The syntax is:

kill spid

You can kill only one process at a time, but you can perform a series of kill commands in a batch. For example:

1> kill 7
2> kill 8
3> kill 9
4> go

A kill command is irreversible and cannot be included in a user-defined transaction. spid must be a numeric constant; you cannot use a variable. Here is some sample output from sp_who:

sp_who

fid  spid  status      loginame  origname  hostname        blk_spid  dbname
        tempdbname    cmd                block_xloid   threadpool
---  ----  ----------  --------  --------  ----------------  --------  ------
        ----------    -----------------  -----------   -------------------
0       1  recv sleep    howard    howard          svr30eng         0  master
            tempdb    AWAITING COMMAND             0      syb_default_pool
0       2  sleeping        NULL      NULL                           0  master
            tempdb    NETWORK HANDLER              0      syb_default_pool
0       3  sleeping        NULL      NULL                           0  master
            tempdb    DEADLOCK TUNE                0      syb_default_pool
0       4  sleeping        NULL      NULL                           0  master
            tempdb    MIRROR HANDLER               0      syb_default_pool
0       5  sleeping        NULL      NULL                           0  master
            tempdb    CHECKPOINT SLEEP             0      syb_default_pool
0       6  sleeping        NULL      NULL                           0  master
            tempdb    HOUSEKEEPER                  0      syb_default_pool
0       7  recv sleep      bill      bill           bigblue         0  master
            tempdb    AWAITING COMMAND             0      syb_default_pool
0       8  recv sleep    wilbur    wilbur             hazel         0  master
            tempdb    AWAITING COMMAND             0      syb_default_pool
0       9  recv sleep      joan      joan          luv2work         0  master
            tempdb    AWAITING COMMAND             0      syb_default_pool
0      10  running        foote     foote          svr47hum         0  master
            tempdb    SELECT                       0      syb_default_master
(10 rows affected, return status = 0)

In the example above, processes 2–6 cannot be killed: they are system processes. The login name NULL and the lack of a host name identify processes them as system processes. NETWORK HANDLER, MIRROR HANDLER, HOUSEKEEPER, and CHECKPOINT SLEEP (or, rarely, CHECKPOINT) always appear in sp_who output. AUDIT PROCESS appears if auditing is available.

Processes 1, 8, 9, and 10 can be killed, since they have the status values “recv sleep,” “send sleep,” “alarm sleep,” and “lock sleep.”

In sp_who output, you cannot tell whether a is “recv sleep” belongs to a user who is using SAP ASE and may be pausing to examine the results of a command, or whether a user has restarted a PC or other terminal, and left a stranded process. Query the sysprocesses table to learn more about questionable processes. For example, this query shows the host process ID and client software used by process 8:

select hostprocess, program_name
    from sysprocesses
where spid = 8

hostprocess program_name
----------- ----------------
3993        isql

This query, plus the information about the user and host from the sp_who results, provides additional information for tracking down the process from the operating system level.