Killing processes

A process is a unit of execution carried out by Adaptive Server. 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 Adaptive Server, 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 kill command gets rid of an ongoing process. 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

Adaptive Server 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.

waitfor delay "10:00"

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:

fid spid status     loginame origname  hostname  blk  dbname cmd
--- ---- ---------  -------- --------  --------  ---  ------ ----------------
0   1   recv sleep  howard   howard    svr30eng  0    master AWAITING COMMAND
0   2   sleeping    NULL     NULL                0    master NETWORK HANDLER
0   3   sleeping    NULL     NULL                0    master DEADLOCK TUNE
0   4   sleeping    NULL     NULL                0    master MIRROR HANDLER
0   5   sleeping    NULL     NULL                0    master CHECKPOINT SLEEP
0   6   sleeping    NULL     NULL                0    master HOUSEKEEPER
0   7   recv sleep  bill     bill      bigblue   0    master AWAITING COMMAND
0   8   recv sleep  wilbur   wilbur    hazel     0    master AWAITING COMMAND
0   9   recv sleep  joan     joan      luv2work  0    master AWAITING COMMAND
0   10  running     foote    foote     svr47hum  0    master SELECT
(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 Adaptive Server 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.