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.
Best Answer
The way I usually go about this is to use the procedure sp_WhoIsActive, and log that to a table. This way I don't have to worry about about the result of the procedure being right, cause I'm sure it is.
There are some great articles on the Brent Ozar website about it.
Brent Ozar logging sp_WhoIsActive
Brent Ozar Monitoring activity
Afterwards you can do whatever you want with that data. You can transfer data from the logging table to the Dxxx.dbo.TDBMSG table, or send mails directly from the WhoIsActive table.