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 processes that are:
Waiting for an alarm, such as a waitfor command
Waiting for network sends or receives
Waiting for a lock
Waiting for synchronization messages from another process in a family
Most running or “runnable” processes
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 will also kill 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.
Table 5-2 shows the 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 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. |
Only a System Administrator 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 not reversible 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 them as system processes. You will always see NETWORK HANDLER, MIRROR HANDLER, HOUSEKEEPER, and CHECKPOINT SLEEP (or, rarely, CHECKPOINT). AUDIT PROCESS becomes activated if you enable auditing.
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 process whose status 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 the process indicates that a user has restarted a PC or other terminal, and left a stranded process.You can learn more about a questionable process by querying the sysprocesses table for information. 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.
