Mikel
来源： 捕获和记录SQLServer2016中发生的死锁_包子大叔的笔记-CSDN博客
1 建表记录死锁日志
–创建阻塞记录表
CREATE TABLE [dbo].[SysBlockLog]
(
[Id] INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[BlockingSessesionId] [smallint] NULL,
[ProgramName] [nchar] (128) NULL,
[HostName] [nchar] (128) NULL,
[ClientIpAddress] [varchar] (48) NULL,
[DatabaseName] [sysname] NOT NULL,
[WaitType] [nvarchar] (60) NULL,
[BlockingStartTime] [datetime2] NOT NULL,
[WaitDuration] [bigint] NULL,
[BlockedSessionId] [int] NULL,
[BlockedSQLText] [nvarchar] (MAX) NULL,
[BlockingSQLText] [nvarchar] (MAX) NULL,
[CreationTime] [datetime2] NOT NULL
)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2 创建数据库作业监控和记录死锁
2.1 给作业命名（要先命名）
2.2 新建步骤，选择数据库，作业脚本如下
INSERT INTO SysBlockLog(BlockingSessesionId, ProgramName, HostName, ClientIpAddress, DatabaseName, WaitType, BlockingStartTime,
WaitDuration, BlockedSessionId, BlockedSQLText, BlockingSQLText, CreationTime)
SELECT wt.blocking_session_id AS BlockingSessesionId, sp.program_name AS ProgramName,
COALESCE(sp.LOGINAME, sp.nt_username) AS HostName, ec1.client_net_address AS ClientIpAddress,
db.name AS DatabaseName, wt.wait_type AS WaitType,
ec1.connect_time AS BlockingStartTime, wt.WAIT_DURATION_MS / 1000 AS WaitDuration,
ec1.session_id AS BlockedSessionId, h1.TEXT AS BlockedSQLText,
h2.TEXT AS BlockingSQLText, getdate()
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
LEFT OUTER JOIN master.dbo.sysprocesses sp ON SP.spid = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2.3 新建“报警”
类型：选择”SQL Server性能条件警报”
对象：SQLServer:General Statistics
计数器：Processes blocked
计数器满足以下条件时触发警报：高于， 值：0
保存作业就可以了
————————————————
版权声明：本文为CSDN博主「zheyiw」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/zheyiw/article/details/103516839
