September 22, 2021 at 8:06 pm
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
September 23, 2021 at 12:07 pm
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
September 23, 2021 at 12:34 pm
Ok thanks a lot, anyone any idea how to code the second part? To actually do the kill
September 23, 2021 at 1:05 pm
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
September 23, 2021 at 3:53 pm
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
Change is inevitable... Change for the better is not.
September 23, 2021 at 7:50 pm
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
September 23, 2021 at 8:35 pm
Is it a stored procedure or ad hoc code or ???
--Jeff Moden
Change is inevitable... Change for the better is not.
September 23, 2021 at 9:23 pm
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.
September 24, 2021 at 12:06 am
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
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply