DPA run time alert

We have created a custom alert in DPA for an SQL Query to send email if running for more than 30 seconds. I have also created the following in SQL Server Agent Job as follows (job fires every min):

DECLARE @counter INT;
SELECT
       @counter = COUNT(*)
FROM
    ( SELECT
        *
      FROM
        ( SELECT
            'TotalViewReportingODS' AS DBNAme
           ,sqltext.TEXT
           ,req.session_id
           ,req.status
           ,req.command
           ,req.cpu_time
           ,req.total_elapsed_time
          FROM
            sys.dm_exec_requests req
          CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
          WHERE
            DB_NAME(req.database_id) = 'TotalViewReportingODS'

        ) allRun
      WHERE
       total_elapsed_time > 30000
       -- cpu_time > 30000
              and text LIKE '%fnReturnCommonData%'
    ) counters

IF @counter > 0
    BEGIN
              --send mail to dbas
        EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBMailing',
            @recipients = List of emails here,

@subject = 'Long runnung function fnReturnCommonData',
@body = 'Long running SQL in totalVIew'

END

I get emails every day from the DPA alert but never from this.

How is the DPA alert gathering data? Is it looking at average running time over a period of just on statement running longer that 30 seconds.

Find more posts tagged with

Accepted answers

All comments

brianflynn

Hello, I checked with support and they suspect this (SolarWinds Knowledge Base :: Alert Notifications) may help. We wonder if your notifications are set incorrectly. My support agent says he suspects it's set to "STATUS_NOT_VISITED" and if it isn't changing, you won't get notified. You may want to set it to "STATUS_NOT_NORMAL". If that doesn't help, I suggest opening a support case. Thanks!

STATUS_NOT_VISITED

Sends notifications if the alert returns a status that is not NORMAL and is a status that has not been returned since the alert was last NORMAL. This is the default notification trigger.

And if it isn’t changing, he won’t get notified.

He may want to set it to:

STATUS_NOT_NORMAL

Sends notifications if the alert returns a status that is not NORMAL regardless of the alert's previous status

jaminsql

garymazzone‌,

Reading through here. Are you saying you get alerts from DPA and not from the SQL Agent job ? Are all the settings for database mail working ? Seems like if the email from DPA is working and the notifications from msdb.dbo.sp_send_dmail are not that could be a good thing to check.

Also you said this was a custom alert so do you have a link the the alert you are talking about or care to share the code here? Perhaps it is this one Custom Alert - Long Running Queries. I think we can better answer if we know what the custom alert code is doing.