Sql Server Kill a SPID (session or Process)
- Posted by Sqltimes
- On April 23, 2014
- 0 Comments
Quick one today:
Sometimes, but not too often (thank fully), we end up in situations where we need to KILL some processes (SPIDs). There are several reasons for this.
- Either a long running query (that is just SELECTing)
- Query that is blocking other processes
- Query is has been in waiting state for a long while, where is is waiting for other resources to free up.
|
1
2
3
4
5
|
---- Kill SPID 54--KILL 54;GO |
After you issue the KILL command, Sql Server starts the rollback process to be able to clear the work performed so far and then terminates the SPID. To check the status of the termination process, you can issue this ‘STATUS’ command:
|
1
2
3
4
5
6
7
8
9
10
|
---- Query the status of a KILL command issues previously--KILL 54 WITH STATUSONLY;GO---- Output looks like this--spid 54: Transaction rollback in progress. Estimated rollback completion: 20% Estimated time left: 50 seconds. |
Hope this helps,
_Sqltimes
0 Comments