asked
Long running transaction alert configuration
Hi, I am trying to setup a alert for long running transactions. The alert is setup to run the below job and then further run the stored procedure to collate data and email. However the SQL_Text column is most of the time blank in the email however I can see results in destination table. Is there any config that I can add here to make sure it is always there. The data is being captured from the Sp_whoisactive. **SQL Agent Job Step**: ------------------------------------------------------------------ DECLARE @AlertingThresholdMinutes int = 1200; SET NOCOUNT ON; DECLARE @LongestRunningTransaction int; SELECT @LongestRunningTransaction = MAX(DATEDIFF(n, dtat.transaction_begin_time, GETDATE())) FROM sys.dm_tran_active_transactions dtat INNER JOIN sys.dm_tran_session_transactions dtst ON dtat.transaction_id = dtst.transaction_id; IF ISNULL(@LongestRunningTransaction,0) > @AlertingThresholdMinutes BEGIN DECLARE @destination_table varchar(500) = 'WhoIsActive', @destination_database sysname = 'DBADB', @schema varchar(max), @SQL nvarchar(4000), @parameters nvarchar(500), @exists bit; SET @destination_table = @destination_database + '.dbo.' + @destination_table; --create the logging table IF OBJECT_ID(@destination_table) IS NULL BEGIN; EXEC sp_WhoIsActive @get_transaction_info = 1, @get_outer_command = 1, @get_plans = 1, @return_schema = 1, @schema = @schema OUTPUT; SET @schema = REPLACE(@schema, '', @destination_table); EXEC(@schema); END; --purge older data SET @SQL = 'DELETE FROM ' + @destination_table +' '; EXEC (@SQL); --collect activity into logging table EXEC dbo.sp_WhoIsActive @get_transaction_info = 1, @get_outer_command = 1, @get_plans = 1, @destination_table = @destination_table; Exec [DBADB].[dbo].[Long_Running_Query_Alert] END **Stored Procedure : Long_Running_Query_Alert** ------------------------------------------------------------------ DECLARE @tableHTML NVARCHAR(MAX) ; SET @tableHTML = N'
A couple of things I can suggest: 1. Replace with in the SQL Server Agent job -- ![alt text][1] (assuming it's just the same issue that I had in #2 but just in case) 2. Add to the stored procedure right after you SET @tableHTML -->![alt text][2] . This will clean up the HTML string a bit. (I could not even used the text in the post because these strings were stripped out, had to use the picture) HTH, Denis [1]: /storage/temp/4350-capture2.jpg [2]: /storage/temp/4348-capture.jpg
Thanks Denis. It worked!! :-). Step 1 was already there but somehow a part of it was taken away when I originally submitted it. Step 2 fixed the missing SQL issue. Thank again for your help. Much appreciated.
DECLARE @xml NVARCHAR(max)
DECLARE @body NVARCHAR(max)
-- specify long running query duration threshold
DECLARE @longrunningthreshold INT
SET @longrunningthreshold = 61
-- step 1: collect long running query details.
;WITH cte
AS (
SELECT [Session_id] = spid
,[Sessioin_start_time] = (
SELECT start_time
FROM sys.dm_exec_requests
WHERE spid = session_id
)
,[Session_status] = Ltrim(Rtrim([status]))
,[Session_Duration] = Datediff(s, (
SELECT start_time
FROM sys.dm_exec_requests
WHERE spid = session_id
), Getdate())
,[Session_query] = Substring(st.TEXT, (qs.stmt_start / 2) + 1, (
(
CASE qs.stmt_end
WHEN - 1
THEN Datalength(st.TEXT)
ELSE qs.stmt_end
END - qs.stmt_start
) / 2
) + 1)
,[Complete_Query]= (st.TEXT)
,[program_name]= qs.program_name
,[hostname] = qs.hostname
,[isblocked] = qs.blocked
FROM sys.sysprocesses qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
WHERE qs.lastwaittype<>'BROKER_RECEIVE_WAITFOR'
AND qs.cmd NOT LIKE '%BACKUP%'
AND qs.cmd NOT LIKE '%INDEX%'
AND qs.cmd NOT LIKE '%RESTORE%'
AND qs.cmd NOT LIKE '%DBCC%'
AND qs.program_name NOT LIKE '%SQLAgent - TSQL JobStep%'
AND qs.hostname NOT LIKE '%RPT01%'
)
-- step 2: generate html table
SELECT @xml = Cast((
SELECT session_id AS 'td'
,''
,session_duration AS 'td'
,''
,session_status AS 'td'
,''
,[session_query] AS 'td'
,''
,[Complete_Query] AS 'td'
,''
,[program_name] AS 'td'
,''
,[hostname] AS 'td'
,''
,[isblocked] AS 'td'
FROM cte
WHERE session_duration >= @longrunningthreshold and cte.Session_query NOT LIKE '%sp_server_diagn%'
FOR XML path('tr')
,elements
) AS NVARCHAR(max))
-- step 3: do rest of html formatting
SET @body = '
<html>
<body><bold>Long Running Queries (longer than 60 sec)</bold>
<table border = 1 BORDERCOLOR="Black">
<tr>
<th align="centre"> Session_id </th>
<th> Duration(sec) </th>
<th> Status </th>
<th> CurrentQuery </th>
<th> CompleteQuery </th>
<th> ProgramName </th>
<th> Hostname </th>
<th> isblocked </th>
</tr>'
SET @body = @body + @xml + '</table></body></html>'
-- step 4: send <g class="gr_ gr_50 gr-alert gr_gramm Grammar only-ins doubleReplace replaceWithoutSep" data-gr-id="50" id="50">email</g> if a long running query is found.
IF (@xml IS NOT NULL)
BEGIN
EXEC msdb.dbo.sp_send_dbmail @profile_name = ''
,@body = @body
,@body_format = 'html'
,@recipients = ''
,@subject = 'ALERT: Long Running Queries on DBServer';
END
19 People are following this question.
