1樓
prcak47 發(fā)表于：2015/6/24 15:42:18
系統(tǒng)的每日結算過程是一個很復雜又很重要的過程
這個過程執(zhí)行時間比較長，偶爾會被傳輸或者不正常的報表操作等等死鎖掉
但是在系統(tǒng)日志中只知道是因為死鎖而失敗，卻不知道因為具體什么事件或者過程而造成了死鎖。
下面這個過程可以記錄死鎖和被死鎖的語句，從而幫助我們分析故障原因，從而拿出具體的解決方法。
首先用下面語句創(chuàng)建表
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))
可以在系統(tǒng)內建立報表，不定時查詢該表數據
然后用下面的語句創(chuàng)建存儲過程
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: <20150619>
-- Description: <記錄死鎖定時任務>
-- =============================================
CREATE PROCEDURE up_log_inputbuffer
AS
BEGIN
/****
需要創(chuàng)建該表
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))
***/
---創(chuàng)建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
存儲過程創(chuàng)建后，可以用該存儲過程建立數據庫定時任務，每分鐘執(zhí)行一次
在營業(yè)結束后開始，日結結束后停止。
這樣通過查詢表 tb_log_inputbuffer 數據就可以知道死鎖的時候所發(fā)生的事情。