Tag Archives: spid

Get and Kill Executing Processes of Job

This query will identify the current SQL processes or executions which belong to a specific job.

We had a job process that was getting blocked for long periods, and in turn was causing a lot of blocking.  The best solution here is to fix the root blocker, however, as a short term fix we want to allow the job to run but then kill it a specific time if it is still running.  To do this, we create another job that looks for all of the SPIDS associated to executions which belong to the job getting blocked, and execute a kill commend for each SPID.

 

Show SP_Who and Rollback Status for All SPIDS Rolling Back

We had a session we had to kill and roll back and wanted some more information about the status of the roll back.  If you only have one SPID to check then you can run the kill commend with an option:

I wanted to expand this by dynamically looking for all sessions in rollback status and then provide some information about the session as well as the kill status.  This way, I do not need to first manually review sessions and look for records in rollback status, then check each kill status.

In my test there was only one SPID in rollback status but it will work if there are more than one.