question

Sagar Bhargava avatar image
Sagar Bhargava 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'

Heading

' + N'' + N'[dd hh:mm:ss.mss]session_id' + N'sql_textlogin_name' + N'host_namedatabase_name' + CAST ( ( SELECT top 10 td = [dd hh:mm:ss.mss], '', td = session_id, '', td = sql_command, '', td = login_name, '', td = host_name, '', td = database_name, '' FROM [DBADB].[dbo].[WhoIsActive] FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'' ; DECLARE @Subject nvarchar(100); SET @subject = '[Warning] Long Running Transaction On ' + @@SERVERNAME; EXEC msdb.dbo.sp_send_dbmail @recipients='-', @subject = @subject, @body = @tableHTML, @body_format = 'HTML' ;
tsqlalerts
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DenisT avatar image
DenisT answered
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

capture.jpg (11.2 KiB)
capture2.jpg (11.2 KiB)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sagar Bhargava avatar image Sagar Bhargava commented ·
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.
0 Likes 0 ·
vickyarea51 avatar image
vickyarea51 answered

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

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.