跟踪标记:1204/1222功能及用途:捕获SQL Server死锁信息,并自动存放到错误日志(ERRORLOG)中。 举例:USE tempdbGO CREATE TABLE t1(id int)INSERT t1 SELECT 1CREATE TABLE t2(id in ...
跟踪标记:1204/1222
功能及用途:
捕获SQL Server死锁信息,并自动存放到错误日志(ERRORLOG)中。
举例:
USE tempdbGO CREATE TABLE t1(id int)INSERT t1 SELECT 1CREATE TABLE t2(id int)INSERT t2 SELECT 1GO --开启1204/1222跟踪标记(任何查询窗口)DBCC TRACEON(1204,-1)--DBCC TRACEON(1205,3605,-1)--DBCC TRACEON(1206,3605,-1)--DBCC TRACEON(1222,-1)GO --查询窗口1SET LOCK_TIMEOUT -1SET TRANSACTION ISOLATION LEVEL READ COMMITTEDBEGIN TRAN UPDATE t1 SET id = 0 WHERE id=1 WAITFOR DELAY '00:00:05' UPDATE t2 SET id = 0 WHERE id=1--COMMIT TRANGO --查询窗口2SET LOCK_TIMEOUT -1SET TRANSACTION ISOLATION LEVEL READ COMMITTED--死锁优先级为low,将被选择作为牺牲品SET DEADLOCK_PRIORITY LOW BEGIN TRAN UPDATE t2 SET id = 0 WHERE id=1 UPDATE t1 SET id = 0 WHERE id=1--COMMIT TRANGO --关闭1204/1222跟踪标记(任何查询窗口)DBCC TRACEOFF(1204,-1)--DBCC TRACEOFF(1205,3605,-1)--DBCC TRACEOFF(1206,3605,-1)--DBCC TRACEOFF(1222,-1)GO --查看错误日志里的死锁信息exec xp_readerrorlog 0,1DROP TABLE t1,t2
原标题:3. 跟踪标记 (Trace Flag) 1204, 1222 抓取死锁信息
关键词:
*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们:
admin#shaoqun.com
(#换成@)。