0

I am trying to setup an alert where the application team can get the notification on their event viewer for any queries which are running longer than 2 minutes. This is what I have so far. For some reason, the application team is not receiving the alert format which they are expecting(spid number,and the exact message text which is in the store proc) Can someone suggest what I am missing here? The time threshold is being passed as 2 minutes from the SQL Agent

create PROCEDURE [dbo].[usp_long_running_Query]
@timeThreshold time --  '00:02:00.0000000'
AS
    BEGIN
        DECLARE @tmpTime time
        DECLARE @dbname varchar(20)
        DECLARE @vmsg varchar(2000)
        DECLARE @vstatus numeric
        DECLARE @vstrstatus varchar(100)
        DECLARE @spid varchar(10)
        declare @hostName varchar(30) = CONVERT(nvarchar(30),SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))
        declare @Date nvarchar(25) = CONVERT(nvarchar(25),GETDATE())
        DECLARE @svr varchar(30) = @@SERVERNAME
    SELECT TOP 1
        right(convert(varchar,dateadd(ms, datediff(ms, P.last_batch, getdate()), '1900-01-01'), 121), 12) as 'batch_duration', DB_NAME(qt.dbid) as 'db_name',P.spid
    INTO #queryTime
    FROM master.dbo.sysprocesses P
        INNER JOIN sys.dm_exec_query_stats b
            ON p.sql_handle = b.sql_handle
        CROSS APPLY sys.dm_exec_sql_text(b.sql_handle) as qt
        LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
    WHERE P.spid > 50
        AND P.status not in ('background', 'sleeping')
        AND P.cmd not in ('AWAITING COMMAND'
                        ,'MIRROR HANDLER'
                        ,'LAZY WRITER'
                        ,'CHECKPOINT SLEEP'
                        ,'RA MANAGER')
        AND DB_NAME(qt.dbid) = 'xxx'
        AND (P.loginame like 'xyz%'
                OR P.loginame like'xyz%'
                OR P.loginame like'xyz%'
                OR P.loginame like'xyz%'
                OR P.loginame like'xyz%'
                OR P.loginame like 'Ipa%')
        --AND o.name = 'usp_OpSec_getScreeningComplianceSummaryMvmDetail'
    ORDER BY batch_duration DESC 

    SET @dbname = (SELECT db_name FROM #queryTime)
    SET @tmpTime = (SELECT batch_duration FROM #queryTime)
    SET @spid = (SELECT spid FROM #queryTime)
    SET @vstatus = (SELECT db_status FROM Dxxx.dbo.TDBSTATUS WHERE db_na = @dbname)
    SET @vstrstatus=CAST(@vstatus as varchar(100))

    IF @tmpTime > @timeThreshold
        BEGIN
            SELECT @vmsg='AlertType=DBMaint Host='+@hostName+' Instance='+@svr+' Date='+@Date+' MSG= Error: 82192 The database ' + @dbname + ' has long running queries > 2 min. Please monitor this database.  The status is ' + @vstrstatus  + '.' + ' The query has been running for ' + CAST(@tmpTime as varchar(50)) + '. The spid of the longest running query is ' + @spid
            INSERT INTO Dxxx.dbo.TDBMSG(DB_NA,MSG_TXT,SEVERITY) VALUES (@dbname,@vmsg,'FATAL')

            SET @vmsg = 'AlertType=DBMaint Host='+@hostName+' Instance='+@svr+' Date='+@Date+' MSG= Error: 82192 The database ' + @dbname + ' has long running queries > 2 min. Please monitor this database.  The status is ' + @vstrstatus  + '.' + ' The query has been running for ' + CAST(@tmpTime as varchar(50)) + '. The spid of the longest running query is ' + @spid
            RAISERROR (82192,17,1,@dbname,@vstrstatus,@vmsg)with nowait
            DROP TABLE #queryTime
        END
    ELSE
        BEGIN
            DROP TABLE #queryTime
        END
END
GO

The expected alert should be something as mentioned below. Please note DB mail is not being used, when raiseerror happens, the alert gets pushed into centralized server and from there the app team receives it in their event viewer

Error: 82192 Severity: 17 State: 1 AlertType=DBMaint Host=xxxx Instance=Cxxxxx Date=May 20 2019  9:45PM MSG= The database has long running queries > 2 min. Please monitor the database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.