CREATE PROCEDURE usp_SqlAgentPoll_Missing
@StartDate SMALLDATETIME = NULL,
@EndDate SMALLDATETIME = NULL,
@recipients NVARCHAR(1000) = 'gvarol@corelogic.com',
@SendEmail BIT = 1
AS
SET NOCOUNT ON
DECLARE @MinStart INT,
@MinEnd INT,
@DateStart DATETIME,
@Body NVARCHAR(MAX),
@subject NVARCHAR(MAX)
IF OBJECT_ID('TEMPDB..#Temp') IS NOT NULL
DROP TABLE #Temp
IF OBJECT_ID('TEMPDB..#Temp2') IS NOT NULL
DROP TABLE #Temp2
CREATE TABLE #Temp (MinNum INT NOT NULL,
CalcDateTime SMALLDATETIME NOT NULL,
RowNum INT NOT NULL)
CREATE TABLE #Temp2 (MinStart INT NOT NULL,
MinEnd INT NOT NULL,
DateStart DATETIME NOT NULL)
SELECT @StartDate = ISNULL(@StartDate, DATEADD(DAY, -1, GETDATE())),
@EndDate = ISNULL(@EndDate, GETDATE()) ;
WITH calc
AS (SELECT digit + 1 AS MinNum,
DATEADD(MINUTE, digit, @StartDate) CalcDateTime
FROM dbo.GetNumbersInline(0, DATEDIFF(MINUTE, @StartDate, @EndDate)))
INSERT #Temp WITH (TABLOCK)
(MinNum,
CalcDateTime,
RowNum)
SELECT *,
ROW_NUMBER() OVER (ORDER BY MinNum) AS RowNum
FROM calc c
WHERE NOT EXISTS ( SELECT *
FROM dbo.SqlAgentPoll
WHERE PollDateTime = CalcDateTime )
WHILE 1 = 1
BEGIN
SELECT TOP 1
@MinStart = a.MinNum,
@MinEnd = b.MinNum,
@DateStart = a.CalcDateTime
FROM (SELECT TOP 1
*
FROM #Temp
WHERE MinNum > ISNULL(@MinEnd, 0)
ORDER BY MinNum) a
INNER JOIN #temp b ON b.MinNum >= a.MinNum
AND b.MinNum - a.MinNum = b.RowNum - a.RowNum
ORDER BY b.MinNum DESC
IF @@ROWCOUNT = 1
BEGIN
INSERT #Temp2 WITH (TABLOCK)
(MinStart, MinEnd, DateStart)
VALUES (@MinStart, @MinEnd, @DateStart)
END
ELSE
BREAK
END
IF @SendEmail = 1
AND @recipients <> ''
BEGIN
SELECT @Body = dbo.StringConcat(CONVERT(VARCHAR(30), DateStart, 101) + ' ' + CONVERT(VARCHAR(5), DateStart, 114)
+ ' - ' + CONVERT(VARCHAR(30), dl.DateEnd, 101) + ' '
+ CONVERT(VARCHAR(5), dl.DateEnd, 114) + ', ' + MissingMinutes, '
'),
@subject = 'SQL Agent missed polls. There are ' + CAST(COUNT(*) AS VARCHAR) + ' Gaps and '
+ CAST(SUM(MinEnd - MinStart + 1) AS VARCHAR) + ' Minutes missing'
FROM #Temp2
CROSS APPLY (SELECT DATEADD(MINUTE, MinEnd - MinStart, DateStart) AS DateEnd,
CAST(MinEnd - MinStart + 1 AS VARCHAR) + ' Minute' + CASE WHEN MinEnd = MinStart THEN ''
ELSE '(s)'
END + ' Missing' AS MissingMinutes) dl
IF @Body <> ''
EXEC msdb.dbo.sp_send_dbmail
@recipients = @recipients,
@subject = @subject,
@body = @Body
END
ELSE
BEGIN
SELECT DateStart,
DATEADD(MINUTE, MinEnd - MinStart, DateStart) AS DateEnd,
MinEnd - MinStart + 1 AS MissingMinutes
FROM #Temp2
END