June 9, 2016 at 11:43 am
All, wondering if I can get some help? I am trying to find a script which can help me accomplish this task. I am trying to have a scheduled job which runs every 5 minutes, looks for any query that's been running for 5 minutes or more than 5 minutes and then kill the session. Thanks for the help in advance.
June 9, 2016 at 11:47 am
Do your users know that you're planning on doing that? It sounds seriously drastic.
If you really want to do this, you can find this data in sys.dm_exec_requests. https://msdn.microsoft.com/en-us/library/ms177648.aspx
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 9, 2016 at 12:11 pm
I foresee failed backups and other maintenance tasks in your future, possibly heavier server load as reports are run again and again as they keep failing, and irate users. Possibly also a tedious data cleanup process when the killed process doesn't roll back properly.
This is not, in general, a good thing to do. If you have long-running processes that are causing problems, tune them so that they're faster.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2016 at 12:31 pm
Why?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 9, 2016 at 12:54 pm
GilaMonster (6/9/2016)
I foresee failed backups and other maintenance tasks in your future, possibly heavier server load as reports are run again and again as they keep failing, and irate users. Possibly also a tedious data cleanup process when the killed process doesn't roll back properly.This is not, in general, a good thing to do. If you have long-running processes that are causing problems, tune them so that they're faster.
This would be for couple of users who runs crazy queries once in a while, which fills up the temp drive. Backup jobs, maintenance tasks won't be affected.
June 9, 2016 at 1:01 pm
Why not educate the users so that they write good queries, rather than annoying them by killing their sessions?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2016 at 1:12 pm
Chitown (6/9/2016)
All, wondering if I can get some help? I am trying to find a script which can help me accomplish this task. I am trying to have a scheduled job which runs every 5 minutes, looks for any query that's been running for 5 minutes or more than 5 minutes and then kill the session. Thanks for the help in advance.
Quick thought, if you were working for me then you probably would be rewriting and updating your CV by now, this definitely is not the way to solve this problem.
😎
June 9, 2016 at 3:20 pm
Eirikur Eiriksson (6/9/2016)
Chitown (6/9/2016)
All, wondering if I can get some help? I am trying to find a script which can help me accomplish this task. I am trying to have a scheduled job which runs every 5 minutes, looks for any query that's been running for 5 minutes or more than 5 minutes and then kill the session. Thanks for the help in advance.Quick thought, if you were working for me then you probably would be rewriting and updating your CV by now, this definitely is not the way to solve this problem.
😎
I couldn't agree with you more. I 'd fire myself as well but I just can't seem to educate users who runs this long query. Even though they pull unnecessary data, they just don't seem to understand. This is my last resort and that's why I was asking for some suggestion. Anyways, this is what I have.
/****** Object: StoredProcedure [dbo].[killLongRunningstatement] Script Date: 6/9/2016 4:18:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Drop procedure LongRunningQueries
ALTER procedure [dbo].[killLongRunningstatement]
AS
SET NOCOUNT ON
-- Table variable to hold InputBuffer data
DECLARE @Inputbuffer TABLE
(
EventType NVARCHAR(30) NULL,
Parameters INT NULL,
EventInfo NVARCHAR(4000) NULL
)
-- Table variable to hold running processes information
DECLARE @BusyProcess TABLE
(
SPID INT,
Status VARCHAR(100),
Login VARCHAR(100),
HostName VARCHAR(100),
DBName VARCHAR(100),
Command VARCHAR(200),
LastBatch DATETIME,
ProgramName VARCHAR(200),
EventInfo NVARCHAR(4000), -- extra column to hold actual stored procedure or batch call text
EventTime INT -- time in minutes, a process is running
)
-- Insert all running processes information to table variable
INSERT @BusyProcess
( SPID, Status, Login, HostName, DBName, Command, LastBatch, ProgramName )
SELECT spid,status,loginame,hostname,DB_NAME(dbid),cmd,last_batch,program_name
FROM SYS.SYSPROCESSES
WHERE
1 = CASE WHEN Status IN ( 'RUNNABLE', 'SUSPENDED' ) THEN 1
--Transactions that are open not yet committed or rolledback
WHEN Status = 'SLEEPING' AND open_tran > 0 THEN 1
ELSE 0 END
AND cmd NOT LIKE 'BACKUP%'
-- Cursor to add actuall Procedure or Batch statement for each process
DECLARE cur_BusyProcess Cursor
FOR SELECT SPID
FROM @BusyProcess
OPEN cur_BusyProcess
DECLARE @SPID INT
Fetch NEXT FROM cur_BusyProcess INTO @SPID
While ( @@FETCH_STATUS <> -1 )
BEGIN
INSERT @Inputbuffer
EXEC ( 'DBCC INPUTBUFFER(' + @SPID + ')'
)
UPDATE @BusyProcess
SET EventInfo = I.EventInfo,
EventTime = DATEDIFF(MI,LastBatch,GETDATE())
FROM @BusyProcess b
CROSS JOIN @Inputbuffer i
WHERE B.SPID = @SPID
DELETE FROM @Inputbuffer
FETCH NEXT FROM cur_BusyProcess INTO @SPID
END
CLOSE cur_BusyProcess
DEALLOCATE cur_BusyProcess
-- Create html mail
IF EXISTS(SELECT 1
FROM @BusyProcess I
WHERE EventInfo NOT LIKE '---BusyProcess Detection%'
AND EventTime >= 3
)
BEGIN
Declare @Body varchar(max), @TableHead varchar(1000), @TableTail varchar(1000)
Set NoCount On;
Set @TableTail = '</table></body></html>';
Set @TableHead = '<html><head>' +
'<style>' +
'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +
'</style>' +
'</head>' +
'<body><table cellpadding=0 cellspacing=0 border=0>' +
'<tr><td align=center bgcolor=#E6E6FA><b>ROW ID</b></td>' +
'<td align=center bgcolor=#E6E6FA><b>SPID</b></td>' +
'<td align=center bgcolor=#E6E6FA><b>Event Info</b></td>' +
'<td align=center bgcolor=#E6E6FA><b>Login</b></td>'+
'<td align=center bgcolor=#E6E6FA><b>DBName</b></td>'+
'<td align=center bgcolor=#E6E6FA><b>Command</b></td>'+
'<td align=center bgcolor=#E6E6FA><b>CPUTime</b></td>'+
'<td align=center bgcolor=#E6E6FA><b>DiskIO</b></td>'+
'<td align=center bgcolor=#E6E6FA><b>LastBatch</b></td>'+
'<td align=center bgcolor=#E6E6FA><b>EventTime</b></td></tr>';
Select @Body = (SELECT td= row_number()over(order by I.SPID ),'',
td=I.SPID,'',
td= I.EventInfo,'',
td= MAX(I.Login),'',
td= I.DBName,'',
td= I.Command,'',
td= I.LastBatch,'',
td= I.EventTime,''
FROM @BusyProcess I
WHERE EventInfo NOT LIKE '---BusyProcess Detection%'
--AND EventTime >= 3
GROUP BY SPID, EventInfo, DBName, Command, LastBatch, EventTime
--HAVING MAX(Login) = 'CureMD'
For XML raw('tr'), Elements
)
-- Replace the entity codes and row numbers
Set @Body = Replace(@Body, '_x0020_', space(1))
Set @Body = Replace(@Body, '_x003D_', '=')
Set @Body = Replace(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
Set @Body = Replace(@Body, '<TRRow>0</TRRow>', '')
Select @Body = @TableHead + @Body + @TableTail
-- Send mail to DBA Team
EXEC msdb.dbo.sp_send_dbmail @recipients='Email address', -- change mail address accordingly
@subject = 'Blocking Session Detected',
@profile_name = 'Default Mail Account', -- Change profile name accordingly
@body = @Body,
@body_format = 'HTML' ;
DECLARE @QKILLsp VARCHAR(1000)
SET @QKILLsp= (SELECT DISTINCT ' KILL '+ CONVERT(VARCHAR,SPID)
FROM @BusyProcess I
WHERE EventInfo NOT LIKE '---BusyProcess Detection%'
AND EventTime >= 15 -- Transactions Running for 15 minutes or more
for XML path('')
)
EXEC(@QKILLsp)
END
June 10, 2016 at 3:29 am
Sounds more like a political issue rather than technical. It would seem you don't have the authority to govern the server in question.
You need to play a political game by e-mailing your concerns to your superiors and those writing the terrible code.
Pointing out what may happen if the DBA is not allowed to managed the server correctly.
If there is no action, send another one stating you cannot guarantee the performance or stability of the server.
This part may sound terrible but you may need the server to encounter issues before they understand what you are saying.
Record all the work you have to do with the server when a problem has encountered and present it to management in a couple of months. This is not best practice but I have worked in places like this, if you keep resolving the issue behind the scenes they will be unaware there is an issue.
(I take it personal if my servers go down) so I know the above will be hard to do for any real DBA.
Most places have no idea what a DBA does or what the responsibilities are.
Is this a reporting server? or OLTP server?
Seems like you have a security,testing, code writing and authority over your environment issue.
Again not ideal but you have a political issue and it requires a political strategy.
I expect to be slated for this post. But this is the real world of IT. Not the test book version.
The other way is to get angry and take the stress and frustration home!!!
Or look for another job where the DBA is listened to and has full authority over the SQL environment..
June 10, 2016 at 3:56 am
Talib123 (6/10/2016)
Sounds more like a political issue rather than technical. It would seem you don't have the authority to govern the server in question.You need to play a political game by e-mailing your concerns to your superiors and those writing the terrible code.
Pointing out what may happen if the DBA is not allowed to managed the server correctly.
If there is no action, send another one stating you cannot guarantee the performance or stability of the server.
This part may sound terrible but you may need the server to encounter issues before they understand what you are saying.
Record all the work you have to do with the server when a problem has encountered and present it to management in a couple of months. This is not best practice but I have worked in places like this, if you keep resolving the issue behind the scenes they will be unaware there is an issue.
(I take it personal if my servers go down) so I know the above will be hard to do for any real DBA.
Most places have no idea what a DBA does or what the responsibilities are.
Is this a reporting server? or OLTP server?
Seems like you have a security,testing, code writing and authority over your environment issue.
Again not ideal but you have a political issue and it requires a political strategy.
I expect to be slated for this post. But this is the real world of IT. Not the test book version.
The other way is to get angry and take the stress and frustration home!!!
Or look for another job where the DBA is listened to and has full authority over the SQL environment..
I'm going to slate you. I completely agree. Users should not be able to run ad hoc queries against live systems. If they need to do this, give them a copy of the database to play with. Anything that passes muster can be implemented on the live server as a stored procedure.
John
June 10, 2016 at 4:08 am
Chitown (6/9/2016)
I just can't seem to educate users who runs this long query. Even though they pull unnecessary data, they just don't seem to understand. This is my last resort
Thing is, if you do this, the users have 'proof' that you're hindering their ability to do their job. They take that to your boss and suddenly you're the bad guy.
This is not a technical problem, don't try to solve it with technology. It's a management and person problem.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 10, 2016 at 7:35 am
This type of situation can be difficult to correct. There is no good answer. You are likely going to meet some serious resistance regardless of what plan you make.
If this is a reporting type of query, I suggest putting up a SSRS instance, creating a report of the data, and use resource governor to throttle it way down.
You can also automate this query to be generated at a given time and send a spreadsheet to or whatever to the users.
if it's ad-hoc-we-just-need-to-see-the-data kind of stuff, can you set up a nightly restore to a "prod-copy" database, and let them do whatever they want in that? They get out of production, you can test the effectiveness of your backup strategy, and everyone still gets to do their craziness.
Regardless of what you put in place, you will need to document everything with facts. You eventually will be able to get things right.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply