系統(tǒng)的每日結(jié)算過(guò)程是一個(gè)很復(fù)雜又很重要的過(guò)程
這個(gè)過(guò)程執(zhí)行時(shí)間比較長(zhǎng)，偶爾會(huì)被傳輸或者不正常的報(bào)表操作等等死鎖掉
但是在系統(tǒng)日志中只知道是因?yàn)樗梨i而失敗，卻不知道因?yàn)榫唧w什么事件或者過(guò)程而造成了死鎖。
下面這個(gè)過(guò)程可以記錄死鎖和被死鎖的語(yǔ)句，從而幫助我們分析故障原因，從而拿出具體的解決方法。
首先用下面語(yǔ)句創(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)內(nèi)建立報(bào)表，不定時(shí)查詢(xún)?cè)摫頂?shù)據(jù)
然后用下面的語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: <20150619>
-- Description: <記錄死鎖定時(shí)任務(wù)>
-- =============================================
CREATE PROCEDURE up_log_inputbuffer
AS
BEGIN
/****
需要?jiǎng)?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臨時(shí)記錄表
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
--刪除沒(méi)有死鎖的記錄
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)
--用游標(biāo)遍歷所有死鎖記錄，查詢(xún)死鎖和被死鎖語(yǔ)句，記錄在表中
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
---查詢(xún)?cè)斐伤梨i的語(yǔ)句
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
--查詢(xún)被死鎖的語(yǔ)句
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
存儲(chǔ)過(guò)程創(chuàng)建后，可以用該存儲(chǔ)過(guò)程建立數(shù)據(jù)庫(kù)定時(shí)任務(wù)，每分鐘執(zhí)行一次
在營(yíng)業(yè)結(jié)束后開(kāi)始，日結(jié)結(jié)束后停止。
這樣通過(guò)查詢(xún)表 tb_log_inputbuffer 數(shù)據(jù)就可以知道死鎖的時(shí)候所發(fā)生的事情。
加好友 
發(fā)短信
Post By：2015/6/24 15:42:18
