□-系统配置管理类-如何能知道日结是被谁死锁导致失败的？[昂捷论坛]

这个过程执行时间比较长，偶尔会被传输或者不正常的报表操作等等死锁掉

但是在系统日志中只知道是因为死锁而失败，却不知道因为具体什么事件或者过程而造成了死锁。

下面这个过程可以记录死锁和被死锁的语句，从而帮助我们分析故障原因，从而拿出具体的解决方法。

create table tb_log_inputbuffer
(c_spid int,
c_blk int,
c_loginame varchar(100),
c_host_name varchar(100),
c_dbname varchar(20),
c_dt datetime,
c_sql_spid varchar(4000),
c_sql_blk varchar(4000))

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: <20150619>
-- Description: <记录死锁定时任务>
-- =============================================
CREATE PROCEDURE up_log_inputbuffer
AS
BEGIN

/****
需要创建该表

drop table tb_log_inputbuffer

create table tb_log_inputbuffer
(c_spid int,
c_blk int,
c_loginame varchar(100),
c_host_name varchar(100),
c_dbname varchar(20),
c_dt datetime,
c_sql_spid varchar(4000),
c_sql_blk varchar(4000))

***/

---创建sp_who临时记录表
create table #p_who
(spid int,
ecid int,
c_status varchar(20),
loginame varchar(100),
hostname varchar(100),
blk int,
dbanme varchar(20),
cmd varchar(100),
request_id int)

--插入sp_who记录
insert into #p_who
exec sp_who

--删除没有死锁的记录
delete #p_who
where blk = 0

--将死锁记录在表中
insert into tb_log_inputbuffer(c_spid,c_blk,c_loginame,c_host_name,c_dbname,c_dt,c_sql_blk,c_sql_spid)
select spid,blk,loginame,hostname,dbanme,GETDATE(),\'\',\'\' from #p_who

declare @blk int
declare @spid int
declare @sql_handle varbinary(64)
declare @sql varchar(4000)

--用游标遍历所有死锁记录，查询死锁和被死锁语句，记录在表中

declare curs_inputbuffer cursor
for
select spid,blk from #p_who

open curs_inputbuffer

fetch curs_inputbuffer into @spid,@blk

while @@fetch_status=0
begin

---查询造成死锁的语句
select @sql_handle = most_recent_sql_handle
from sys.dm_exec_connections
where session_id = @blk

select @sql = [text] from sys.dm_exec_sql_text(@sql_handle)

update tb_log_inputbuffer set c_sql_blk = @sql
where c_spid = @spid

--查询被死锁的语句
select @sql_handle = most_recent_sql_handle
from sys.dm_exec_connections
where session_id = @spid

select @sql = [text] from sys.dm_exec_sql_text(@sql_handle)

update tb_log_inputbuffer set c_sql_spid = @sql
where c_spid = @spid

fetch curs_inputbuffer into @spid,@blk
end

close curs_inputbuffer
deallocate curs_inputbuffer

END
GO

存储过程创建后，可以用该存储过程建立数据库定时任务，每分钟执行一次

这样通过查询表 tb_log_inputbuffer 数据就可以知道死锁的时候所发生的事情。