2 Replies Latest reply on Mar 17, 2015 1:48 PM by jaminsql

    DPA run time alert

    garymazzone

      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.

        • Re: DPA run time alert

          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

          • Re: DPA run time alert
            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.