捕获和记录SQLServer2016中发生的死锁_sqlserver 捕获锁记录

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

保存作业就可以了