Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Coalesces error messages from SQL Error Log into one row per exception
DECLARE @MinutesBackToCheck INT = 10;
SET NOCOUNT ON;
DECLARE @start DATETIME;
SET @start=DATEADD(MINUTE,-@MinutesBackToCheck,GETDATE());
DECLARE @errors AS TABLE
(
ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
LogDate DATETIME,
ProcessInfo NVARCHAR (10),
Error NVARCHAR(MAX)
);
INSERT INTO @errors(LogDate,ProcessInfo,Error)
EXEC master..xp_readerrorlog 0, 1, NULL, NULL, @start, NULL, 'Desc';
--DECLARE @Alerts AS TABLE
--(
-- LogDate DATETIME,
-- ProcessInfo NVARCHAR (10),
-- Error NVARCHAR(MAX)
--)
;WITH logs
AS
(
SELECT
head.ID AS RootID
,head.ID
,head.LogDate
,head.ProcessInfo
,CONVERT(nvarchar(max), head.Error) AS Error
, 1 AS Lvl
FROM @errors as head
WHERE
head.Error LIKE N'Error:%Severity: %'
UNION ALL
SELECT
head.RootID
,tail.ID
,tail.LogDate
,tail.ProcessInfo
,CONVERT(nvarchar(max), head.Error + CHAR(13) + CHAR(10) + tail.Error)
,head.Lvl + 1
FROM logs as head
INNER JOIN @errors as tail
ON head.ProcessInfo = tail.ProcessInfo
AND head.LogDate = tail.LogDate
AND head.Error <> tail.Error
AND head.ID > tail.ID
)
--INSERT INTO @Alerts
SELECT
LogDate,
ProcessInfo,
Error
FROM
(
SELECT *,
RowRank = ROW_NUMBER() OVER (PARTITION BY RootID ORDER BY Lvl DESC)
FROM logs
) AS d
WHERE RowRank = 1
OPTION (MAXRECURSION 0);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.