死锁跟踪

死锁跟踪

-- 定义参数
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

-- 初始化跟踪
exec @rc = sp_trace_create @TraceID output, 0 , N ' d:\log\deadlock ' , @maxfilesize , NULL
-- 此处的d:\log\deadlock是文件名（可自行修改），SQL会自动在后面加上.trc的扩展名
if ( @rc != 0 ) goto error

-- 设置跟踪事件
declare @on bit
set @on = 1
-- 下述语句中的148指的是locks:deadlock graph事件(参见sys.trace_events),12指的是spid列(参见sys.trace_columns)
exec sp_trace_setevent @TraceID , 148 , 12 , @on
exec sp_trace_setevent @TraceID , 148 , 11 , @on
exec sp_trace_setevent @TraceID , 148 , 4 , @on
exec sp_trace_setevent @TraceID , 148 , 14 , @on
exec sp_trace_setevent @TraceID , 148 , 26 , @on
exec sp_trace_setevent @TraceID , 148 , 64 , @on
exec sp_trace_setevent @TraceID , 148 , 1 , @on

-- 启动跟踪
exec sp_trace_setstatus @TraceID , 1

-- 记录下跟踪ID，以备后面使用
select TraceID = @TraceID
goto finish

error:
select ErrorCode = @rc

finish:
go


-- 0 暂停
exec sp_trace_setstatus 2 , 0

-- 2 停止
exec sp_trace_setstatus 2 , 2

-- 查看
select * from fn_trace_gettable( ' d:\log\deadlockdetect.trc ' , 1 )

死锁模拟：

　　连接1：

　　

BEGIN TRAN
UPDATE b SET NAME = ' 2 '
WAITFOR DELAY ' 00:00:10 '
SELECT * FROM dbo.A
ROLLBACK

 　　连接2：

BEGIN TRAN
UPDATE A SET NAME = ' 2 '
WAITFOR DELAY ' 00:00:10 '
SELECT * FROM dbo.B
ROLLBACK

 profiler 打开跟踪文件：

 

方法二：

　　

-- 开启跟踪标志
/*
3605：将DBCC的结果输出到错误日志
1204:返回参与死锁的锁的资源和类型，以及受影响的当前命令。
作用域：仅全局
1222:以不符合任何 XSD 架构的 XML 格式，返回参与死锁的锁的资源和类型，以及受影响的当前命令。
作用域：仅全局
*/
DBCC TRACEON ( 3605 , 1204 , 1222 , - 1 )


-- 关闭跟踪标志
DBCC TRACEOFF( 3605 , 1204 , 1222 , - 1 )

-- 确定哪些跟踪标志当前是活动的
DBCC TRACESTATUS

-- 建立表：
CREATE TABLE [ dbo ] . [ DeadLockLog ] (
[ id ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ LogDate ] [ datetime ] NULL ,
[ ProcessInfo ] [ varchar ] ( 10 ) NULL ,
[ ErrorText ] [ varchar ] ( max ) NULL
)


-- 建立一个job：deadlock 执行以下代码：

-- 新建临时表
IF OBJECT_ID ( ' tempdb.dbo.#ErrorLog ' ) IS Not Null
DROP TABLE #ErrorLog

CREATE TABLE #ErrorLog (Id int IDENTITY ( 1 , 1 ) NOT NULL , a DATETIME , b VARCHAR ( 10 ), c VARCHAR ( MAX ))

-- 将当前日志记录插入临时表
INSERT INTO #ErrorLog EXEC master.dbo.sp_readerrorlog

-- 将死锁信息插入用户表
insert DeadLockLog
select a, b, c
from #ErrorLog
where id >= ( select MAX (id) from #ErrorLog WHERE c Like ' %Deadlock encountered% ' )

DROP TABLE #ErrorLog

接着新建警报:
名称：DeadLockAlert
类型：sqlserver性能条件警报
对象:SQLServer:Locks
计数器：Number OF  Deadlocks/sec
实例：_Total
满足条件：高于  值 0  
响应：选择执行作业 [deadlock]

查看死锁信息:

SELECT * FROM  dbo.DeadLockLog

posted on 2011-05-04 14:59  Henry.Lau 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/Henry1225/archive/2011/05/04/2036520.html