死锁跟踪

-- 定义参数  
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