Capture blocking

--Table: /****** Object: Table [dbo].[NK_BlockingReport] Script Date: 12/21/2022 12:25:22 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[NK_BlockingReport]( [ID] [INT] IDENTITY(1,1) NOT NULL, [ObjectName] [sysname] NULL, [BlockingObjectName] [sysname] NULL, [StatementText] [VARCHAR](MAX) NULL, [BlockingStatementText] [VARCHAR](MAX) NULL, [SessionID] [INT] NULL, [BlockingSessionID] [INT] NULL, [Status] [VARCHAR](50) NULL, [BlockingStatus] [VARCHAR](50) NULL, [DateCreated] [DATETIME] NULL, [DatabaseName] [VARCHAR](100) NULL, [CPU] [BIGINT] NULL, [RunningTime] [INT] NULL, [RuuningFrom] [VARCHAR](800) NULL, [RuuningBy] [VARCHAR](800) NULL, [ProgramName] [VARCHAR](100) NULL, [LoginName] [VARCHAR](100) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO --SP: /****** Object: StoredProcedure [dbo].[NK_SP_InsertBlockingRecords] Script Date: 12/21/2022 12:24:59 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --Schedule Job to run sp and send email when alert happens ALTER PROC [dbo].[NK_SP_InsertBlockingRecords] AS INSERT INTO dbo.NK_BlockingReport (ObjectName,BlockingObjectName, StatementText,BlockingSessionID,BlockingStatementText, BlockingStatus,DateCreated,[Status],DatabaseName,CPU,RunningTime,RuuningFrom,RuuningBy,ProgramName,LoginName) SELECT W1.ObjectName AS ObjectName, W2.ObjectName AS BlockingObjectName, W1.Statement_Text AS BlockedStatementText, W2.sessionid AS BlockingSessionID, W2.Statement_Text AS BlockingStatementText, W2.status AS BlockingStatus, GETDATE() AS DateCreated, w1.[Status] AS CurrentStatus, w1.DatabaseName , w1.[CPU_time] , w2.[RunningMinutes] , w2.[RunningFrom] , w1.[RunningBy] , w1.[Program_Name] , w1.[Login_Name] FROM NK_SP_Active W1 INNER JOIN NK_SP_Active W2 ON W1.BlockingWith > 0 AND W1.BlockingWith = W2.sessionid; job: DECLARE @ServerName NVARCHAR(256) = @@SERVERNAME DECLARE @RoleDesc NVARCHAR(60) SELECT @RoleDesc = a.role_desc FROM sys.dm_hadr_availability_replica_states AS a JOIN sys.availability_replicas AS b ON b.replica_id = a.replica_id WHERE b.replica_server_name = @ServerName IF @RoleDesc = 'PRIMARY' BEGIN EXEC NK_SP_InsertBlockingRecords END ELSE BEGIN RETURN END