Kills a process.
kill spid with statusonly
is the identification number of the process you want to kill. spid must be a constant; it cannot be passed as a parameter to a stored procedure or used as a local variable. Use sp_who to see a list of processes and other information.
reports on the progress of a server process ID (spid) in rollback status. It does not terminate the spid. The statusonly report displays the percent of rollback completed and the estimated length of time in seconds before the rollback completes.
Kills process number 1378:
kill 1378
Reports on the process of the rollback of spid number 13:
kill 13 with statusonly
spid: 13 Transaction rollback in progress. Estimated rollback completion: 17% Estimated time left: 13 seconds spid: 13 Transaction rollback in progress. Estimated rollback completion: 29% Estimated time left: 9 seconds spid: 13 Transaction rollback in progress. Estimated rollback completion: 40% Estimated time left: 8 seconds spid: 13 Transaction rollback in progress. Estimated rollback completion: 47% Estimated time left: 7 seconds spid: 13 Transaction rollback in progress. Estimated rollback completion: 55% Estimated time left: 6 seconds spid: 13 Transaction rollback in progress. Estimated rollback completion: 65% Estimated time left: 5 seconds spid: 13 Transaction rollback in progress. Estimated rollback completion: 73% Estimated time left: 4 seconds spid: 13 Transaction rollback in progress. Estimated rollback completion: 76% Estimated time left: 3 seconds spid: 13 Transaction rollback in progress. Estimated rollback completion: 83% Estimated time left: 2 seconds spid: 13 Transaction rollback in progress. Estimated rollback completion: 94% Estimated time left: 0 seconds
If the rollback of the spid has completed when you issue kill...statusonly or if Adaptive Server is not rolling back the specified spid, kill...statusonly returns the following message:
Status report cannot be obtained. KILL spid:nn is not in progress.
To get a report on the current processes, execute sp_who. Following is a typical report:
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. Table 1-28 shows the status values and the effects of sp_who:
To get a report on the current locks and the spids of the processes holding them, use sp_lock.
ANSI SQL – Compliance level: Transact-SQL extension.
kill permission defaults to System Administrators and is not transferable.
Commands shutdown
System procedures sp_lock, sp_who
Copyright © 2005. Sybase Inc. All rights reserved. |