KILL spid (WITH STATUSONLY)
This article talks about how to stop or terminate a process that is running. Stopping a process shouldn't be the first step when troubleshooting as it can possibly make the situation worse. However, it can sometimes be helpful in clearing blocking chains or other performance issues.
In order to know what to stop, you will need to know what processes or queries are running. There are a variety of ways to see all of the processes or queries running right now, including the following:
- Microsoft built in tools - sp_who and sp_who2
- Click here for Kendra's explanation on sp_who2
- Adam Mechanic's tool - Who is Active (Click here to download it)
- Hand made query using the sys.dm_exec_requests and/or the sys.dm_exec_sessions tables
Once you know the session ID (spid) of the query or process you want to stop, you can do so by using the KILL command. The kill command is pretty straight forward. The syntax is simply: KILL spid (replace the text "spid" with the number of the process desired to be stopped)
A word of caution here, there are times when it is best not to kill a session. One example is if a process has been running for a while and using multiple threads, when it is killed, the rollback operation will only be single threaded. Therefore, the rollback could take much longer than letting the process finish on its own. All of the same performance issues and/or blocking will still continue, so it actually could things worse, not better.
Some processes are not able to be stopped: per Microsoft, "System processes and processes running an extended stored procedure cannot be terminated."
UNIT OF WORK ID
The kill command can also be used to stop or terminate orphaned distributed transactions. The GUID value that is the Unit of Work ID (UOW ID) can be found in either the error log, the MS DTC monitor, or the sys.dm_tran_locks dmv.
The syntax is simply: KILL uowid
While I knew about the kill command, I wasnt aware that you are able to check the status of the rollback process with the WITH STATUSONLY option until semi-recently.
The syntax is simple KILL spid WITH STATUSONLY - or - KILL uowid WITH STATUSONLY
For processes that are in the middle of being rolled back, this will show the status of the rollback. This option doesn't "do" anything, it just shows the progress. Keep in mind that as with other Microsoft provided durations, it isn't an exact number. This is more of a ball park figure that might be semi-right, but may also be a bit off from the actual time.
If you run this option on a spid that isnt part of a rollback, it wont hurt anything. You will just see an error returned indicating a rollback isnt in progress.
Its funny, but whenever I have to kill a process (which is thankfully rare), I cant help but do a bad-guy cackle in my head... lol!
RIP Cursor - took you down from 90 mins to 5 mins
I finished something recently, that I am very proud of - I literally had to hold on to my chair at my desk to not jump up and shout Wooo-Hooo! I tore into a cursor and obliterated it!! :) I wanted to talk about the process of the transformation - just thought I would record my thoughts so that hopefully they will help someone.
I work in the health care industry, and the cursor is used to calculate the number of active patients for each day.The dataset has an admit date and a discharge date, along with some other things. Due to the way the transactional database is set up, there are important conditions to make sure you get the correct insurance, etc. for past dates and such. The total number of active patients can change due to late data entry and/or mistakes, therefore the entire summary table is re-built each night.