Canned Alert for Long running SQL excluding

score 19
Voted on 19 times. You have not voted. Open for Voting

I had to create this custom alert and I feel should be an out of the box basic alert that should be included in the product.

 

SELECT Distinct

'LoginName: '+RTRIM(CONVERT(VARCHAR(50),s.loginame))+', Hostname: '+RTRIM(CONVERT(VARCHAR(50),s.hostname))+', ProgramName: '+RTRIM(CONVERT(VARCHAR(200),s.program_name))+', SPID: '+RTRIM(CONVERT(VARCHAR,s.spid))

--+char(10)+'Duration: '+CONVERT(VARCHAR,DATEDIFF(SS, r.start_time, CURRENT_TIMESTAMP))+' seconds.'+char(10)

+CHAR(10)+convert(varchar(MAX),b.text)

, CONVERT(VARCHAR,DATEDIFF(SS, r.start_time, CURRENT_TIMESTAMP))

FROM master..sysprocesses as s with(NOLOCK)

LEFT OUTER JOIN sys.dm_exec_requests AS r WITH(NOLOCK) 

   ON (s.spid = r.session_id)

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) b

WHERE r.status <> 'background'

AND DATEDIFF(SS, r.start_time, CURRENT_TIMESTAMP) > 600 --10 minutes

and s.spid not in (select distinct s.spid

  from master..sysprocesses as s with(NOLOCK)

  where s.loginame = 'domain\username') -- I use this to exclude a specific user and any multiple threads for the same spid where loginame isnt provided.

Comments

Vote history

SolarWinds uses cookies on its websites to make your online experience easier and better. By using our website, you consent to our use of cookies. For more information on cookies, see our cookie policy.