Script to find session running SQL text and kill session

Question

Script to find session running SQL text and kill session

caz100

SSCommitted

Points: 1827
More actions
September 22, 2021 at 8:06 pm

#3931984

Hi
Looking for a SQL script I can run as an agent job that will search for any active sessions running a piece of SQL code and kill the session or sessions running that code if found.
If anyone could enlighten?
Thanks in advance

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 1

You'll want to reference sys.dm_exec_requests. That's the DMV that shows actively running queries. Combine it with sys.dm_exec_sql_text to search for a string that's currently running something like this:

SELECT der.session_id
FROM sys.dm_exec_requests AS der
    CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest
WHERE dest.text LIKE '%SELECT *%';

That will get you the list of session_id values. Put that into a temp table or table variable or a cursor and then kill them them one at a time.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

caz100 SSCommitted Points: 1827 More actions · Answer 2

Ok thanks a lot, anyone any idea how to code the second part? To actually do the kill

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 3

It's just a loop that's needed. Try using the SELECT statement above and create a cursor. Then execute an a dynamic statement with the kill and the session_id value.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

caz100 SSCommitted Points: 1827 More actions · Answer 4

Ok thanks but if I knew how to do that I wouldn't be on here....maybe someone else has an idea. ...or there is a similar script out there I have not found.

This reply was modified 2 years, 7 months ago by caz100.

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 5

Although sometimes unavoidable, killing sessions is actually a pretty dangerous thing to do in SQL Server. Killing sessions automatically is about 3 times worse.

Prevention and privs control is the best bet. What is it that you're trying to prevent?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

caz100 SSCommitted Points: 1827 More actions · Answer 6

its a temporary solution to an unavoidable issue of blocking ....don't want to kill all sessions blocking just this one running this code. If anyone knows how to script it all please divulge.

Surprised there isn't a script out there for this ...

Thanks in advance

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 7

Is it a stored procedure or ad hoc code or ???

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

caz100 SSCommitted Points: 1827 More actions · Answer 8

A piece of code , not a stored procedure but kicked off by an app at wrong time of day and no idea how to turn off from app.

Could put in a Job to run during business hours and kill and if it occurs if I knew how., maybe I need to post this to dev group as its more scripting.

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 9

Ok. So what in the code uniquely identifies that code and ONLY that code? Which DB_ID does it run against? What is the USER_ID the code from the front end runs as?

I ask these questions to help you write a bit of code where the cure won't actually be worse than the cause.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)