Alerts Database Scripts

Failed Blocks That Have Reached Their Retry Limit

SELECT TOP 1000 T.ApplicationName
		,T.TaskName
		,TE.StartedAt
		,TE.TaskExecutionId
		,CASE WHEN B.BlockType IS NULL THEN 'No Blocks'
			WHEN B.BlockType = 1 THEN 'NumericRange'
			WHEN B.BlockType = 2 THEN 'DateRange'
			WHEN B.BlockType = 3 THEN 'List'
			WHEN B.BlockType = 3 THEN 'Object' ELSE 'Not Defined' END AS BlockType
		,BE.BlockExecutionId
		,B.BlockId
		,CASE WHEN B.BlockType IS NULL THEN '-'
			WHEN B.BlockType = 1 THEN CAST(B.FromNumber AS VARCHAR(50))
			WHEN B.BlockType = 2 THEN CONVERT(VARCHAR(20), B.FromDate, 20)
			WHEN B.BlockType = 3 THEN '-'
			WHEN B.BlockType = 4 THEN '-' ELSE 'X' END AS BlockFrom
		,CASE WHEN B.BlockType IS NULL THEN '-'
			WHEN B.BlockType = 1 THEN CAST(B.ToNumber AS VARCHAR(50))
			WHEN B.BlockType = 2 THEN CONVERT(VARCHAR(20), B.ToDate, 20)
			WHEN B.BlockType = 3 THEN '-'
			WHEN B.BlockType = 4 THEN '-' ELSE 'X' END AS BlockTo
		,BE.Attempt
		,TE.FailedTaskRetryLimit
FROM [Taskling].[TaskExecution] TE WITH(NOLOCK) 
JOIN [Taskling].[TaskDefinition] T WITH(NOLOCK) ON T.TaskDefinitionId = TE.TaskDefinitionId
JOIN [Taskling].[BlockExecution] BE WITH(NOLOCK) ON TE.TaskExecutionId = BE.TaskExecutionId
JOIN [Taskling].[Block] B WITH(NOLOCK) ON BE.BlockId = B.BlockId
WHERE BlockExecutionStatus = 4
AND BE.Attempt = TE.FailedTaskRetryLimit + 1
AND TE.StartedAt >= DATEADD(DAY, -1, GETUTCDATE())
ORDER BY TE.StartedAt desc

Dead Blocks That Have Reached Their Retry Limit

Works for both both death detection modes.

SELECT T.ApplicationName
		,T.TaskName
		,TE.StartedAt
		,TE.TaskExecutionId
		,CASE WHEN B.BlockType IS NULL THEN 'No Blocks'
			WHEN B.BlockType = 1 THEN 'NumericRange'
			WHEN B.BlockType = 2 THEN 'DateRange'
			WHEN B.BlockType = 3 THEN 'List'
			WHEN B.BlockType = 3 THEN 'Object' ELSE 'Not Defined' END AS BlockType
		,BE.BlockExecutionId
		,B.BlockId
		,CASE WHEN B.BlockType IS NULL THEN '-'
			WHEN B.BlockType = 1 THEN CAST(B.FromNumber AS VARCHAR(50))
			WHEN B.BlockType = 2 THEN CONVERT(VARCHAR(20), B.FromDate, 20)
			WHEN B.BlockType = 3 THEN '-'
			WHEN B.BlockType = 4 THEN '-' ELSE 'X' END AS BlockFrom
		,CASE WHEN B.BlockType IS NULL THEN '-'
			WHEN B.BlockType = 1 THEN CAST(B.ToNumber AS VARCHAR(50))
			WHEN B.BlockType = 2 THEN CONVERT(VARCHAR(20), B.ToDate, 20)
			WHEN B.BlockType = 3 THEN '-'
			WHEN B.BlockType = 4 THEN '-' ELSE 'X' END AS BlockTo
		,CASE TE.TaskDeathMode
			WHEN 0 THEN
				DATEDIFF(SECOND, TE.LastKeepAlive, GETUTCDATE()) 
			WHEN 1 THEN
				DATEDIFF(SECOND, TE.StartedAt, GETUTCDATE()) 
			ELSE '-1' END AS SecondsTimeSince
		,CASE TE.TaskDeathMode
			WHEN 0 THEN
				TE.KeepAliveDeathThreshold
			WHEN 1 THEN
				TE.OverrideThreshold
			ELSE '-1' END AS Threshold
		,BE.Attempt
		,TE.DeadTaskRetryLimit AS RetryLimit
FROM [Taskling].[BlockExecution] BE WITH(NOLOCK)
JOIN [Taskling].[TaskExecution] TE WITH(NOLOCK) ON BE.TaskExecutionId = TE.TaskExecutionId
JOIN [Taskling].[TaskDefinition] T WITH(NOLOCK) ON T.TaskDefinitionId = TE.TaskDefinitionId
JOIN [Taskling].[Block] B WITH(NOLOCK) ON BE.BlockId = B.BlockId
WHERE BE.BlockExecutionStatus IN (1,2)
AND TE.StartedAt >= DATEADD(DAY, -1, GETUTCDATE())
AND ((TE.TaskDeathMode = 0
AND DATEDIFF(SECOND, TE.LastKeepAlive, GETUTCDATE()) > DATEDIFF(SECOND, '00:00:00', TE.KeepAliveDeathThreshold))
OR (TE.TaskDeathMode = 1
AND TE.StartedAt <= DATEADD(SECOND, -1 * DATEDIFF(SECOND, '00:00:00', OverrideThreshold), GETUTCDATE())))