Track Blocking and insert in to a table: when blocking happens

 

 ---1 create table to log info

 USE [AuditDB]

GO

/****** Object:  Table [dbo].[NK_BlockingReport]    Script Date: 8/20/2021 7:46:13 AM ******/

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

---2. Create view:

USE [AuditDB]

GO

/****** Object:  View [dbo].[NK_SP_Active]    Script Date: 8/20/2021 7:49:26 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [dbo].[NK_SP_Active]

AS

SELECT

OBJECT_NAME(objectid) AS ObjectName

,SUBSTRING(stateText.text, (statement_start_offset/2)+1,

((CASE statement_end_offset

WHEN -1 THEN DATALENGTH(stateText.text)

ELSE statement_end_offset

END - statement_start_offset)/2) + 1) AS statement_text

,DB_NAME(sess.database_id) AS DatabaseName

,req.cpu_time AS CPU_Time

,DATEDIFF(MINUTE, last_request_start_time, GETDATE()) AS RunningMinutes

,req.Percent_Complete

,sess.HOST_NAME AS RunningFrom

,LEFT(CLIENT_INTERFACE_NAME, 25) AS RunningBy

,sess.session_id AS SessionID

,req.blocking_session_id AS BlockingWith

,req.reads

,req.writes

,sess.[program_name]

,sess.login_name

,sess.status

,sess.last_request_start_time

,req.logical_reads

FROM

sys.dm_exec_requests req

INNER JOIN sys.dm_exec_sessions sess ON sess.session_id = req.session_id

AND sess.is_user_process = 1

CROSS APPLY

sys.dm_exec_sql_text(sql_handle) AS stateText

GO

---3.Create  job 

    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;

 

---4. Create alert --MANUALY  BEST, instaed of script 

USE [msdb]

GO

/****** Object:  Alert [NK_Report Blocked Processes]    Script Date: 8/20/2021 7:44:40 AM ******/

EXEC msdb.dbo.sp_add_alert @name=N'NK_Report Blocked Processes', 

@message_id=0, 

@severity=0, 

@enabled=1, 

@delay_between_responses=60, 

@include_event_description_in=1, 

@notification_message=N'Blocking Occured please look at BlockingReport in AuditDB', 

@category_name=N'[Uncategorized]', 

@performance_condition=N'MSSQL$DBA_TEST:General Statistics|Processes blocked||>|1', 

@job_id=N'6095582e-4202-497b-af3c-1f7842383739'

GO