June 1, 2022 at 9:47 pm
Hello
I am looking for a way to do an autokill for all requests launched from sql server management studio and which exceeds one hour
thanks for your help
thanks
June 2, 2022 at 1:00 pm
Oof.
Well, part of it is easy & straight forward. Set up a SQL Agent job to run once an hour. You can script out the capture of long running code there and then issue kill statements. Probably load it into a table variable then run a loop against that to issue the kill commands (they have to go one at a time).
Now, capturing it, slightly harder. Something like this:
SELECT des.session_id
FROM sys.dm_exec_requests AS der
JOIN sys.dm_exec_sessions AS des
ON der.session_id = des.session_id
WHERE des.program_name LIKE 'Microsoft SQL Server Management Studio%'
AND der.start_time < DATEADD(hh, -1,GETUTCDATE());
Although, I'm awfully uncomfortable with this. Why are so many uncontrolled connections coming from SSMS? Sounds like security is something concentrate on, not killing sessions. Oh, and killing sessions, means rollbacks. Lots and lots of resource use there. Honestly, I don't recommend what you're trying to do. Understand the problem better. KILL commands are a very blunt approach indeed to problems in the system.
"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
June 2, 2022 at 1:08 pm
On top of Grants replay, you should probably be using last_request_start_time to avoid killing sessions which are still actively used.
( maybe killing one of your sessions with which you are trying to figure out what is going on )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 2, 2022 at 4:08 pm
Hello I am looking for a way to do an autokill for all requests launched from sql server management studio and which exceeds one hour thanks for your help
thanks
That'll be fun to watch when someone goes to the CEO and says they can't produce a report for them because their job keeps getting killed. You'll be able to add "Able to piss of multple high level managers with a single act" to your resume.
What NEEDs to happen is to figure out which jobs are taking so long and help people fix them. Just killing off long-runners isn't solving anything. It's a move that only BSOFHs make. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
June 2, 2022 at 5:36 pm
If the backup takes longer than an hour, we're killing it? I mean, so much of this seems arbitrary and heavy-handed. What's the real problem?
"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
June 2, 2022 at 5:48 pm
Understand the problem better. KILL commands are a very blunt approach indeed to problems in the system.
In all fairness if the commands are coming from SSMS it's not problems with the system, it's something the user is doing.
But you should understand exactly what the problem is. For example if it's just people running adhoc queries you could limit it to only selects.
June 2, 2022 at 7:00 pm
Grant Fritchey wrote:Understand the problem better. KILL commands are a very blunt approach indeed to problems in the system.
In all fairness if the commands are coming from SSMS it's not problems with the system, it's something the user is doing.
But you should understand exactly what the problem is. For example if it's just people running adhoc queries you could limit it to only selects.
What the heck are users doing with SSMS? Also, if it is just users (in SSMS, still, why?), they shouldn't have privileges to cause pain, or not so much that we're automatically killing open transactions from SSMS that are over an hour old. Yeesh. Lots of fail up to this point.
"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
June 2, 2022 at 7:30 pm
ZZartin wrote:Grant Fritchey wrote:Understand the problem better. KILL commands are a very blunt approach indeed to problems in the system.
In all fairness if the commands are coming from SSMS it's not problems with the system, it's something the user is doing.
But you should understand exactly what the problem is. For example if it's just people running adhoc queries you could limit it to only selects.
What the heck are users doing with SSMS? Also, if it is just users (in SSMS, still, why?), they shouldn't have privileges to cause pain, or not so much that we're automatically killing open transactions from SSMS that are over an hour old. Yeesh. Lots of fail up to this point.
Well for one thing dm_exec_request doesn't just show sessions with open transactions, it shows any session doing something. Which is a very good reason to know what you're trying to accomplish, wouldn't want to accidently kill a backup or something(or maybe you do :P).
I guess it depends on your organization but we have a number of analyst type people who use SSMS to do analysis, and selects might not open transactions but they sure can put locks on things.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply