系统的每日结算过程是一个很复杂又很重要的过程
这个过程执行时间比较长，偶尔会被传输或者不正常的报表操作等等死锁掉
但是在系统日志中只知道是因为死锁而失败，却不知道因为具体什么事件或者过程而造成了死锁。
下面这个过程可以记录死锁和被死锁的语句，从而帮助我们分析故障原因，从而拿出具体的解决方法。
首先用下面语句创建表
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 数据就可以知道死锁的时候所发生的事情。