1 跟踪死锁和释放死锁的SQL脚步
2 /****************************************************/
3 /* Created by: SQL Server Profiler 2005 */
4 /* Date: 2013/02/13 10:30:02 */
5 /****************************************************/
6
7 USE master
8 GO
9 -- Create a Queue
10 declare @rc int
11 declare @TraceID int
12 declare @maxfilesize bigint
13 set @maxfilesize = 5 --请把他改成需要的最大大小
14 exec @rc = sp_trace_create @TraceID output, 2, N'D:\跟踪死锁.trc', @maxfilesize, NULL
15 --请把他改成服务器上要存放Trace文件的地方 D:\跟踪死锁.trc不用在D盘先新建一个.trc文件SQL会自动新建
16 if (@rc != 0) goto error
17
18 -- Client side File and Table cannot be scripted
19
20 -- Set the events
21 declare @on bit
22 set @on = 1
23 exec sp_trace_setevent @TraceID, 24, 7, @on
24 exec sp_trace_setevent @TraceID, 24, 15, @on
25 exec sp_trace_setevent @TraceID, 24, 55, @on
26 exec sp_trace_setevent @TraceID, 24, 8, @on
27 exec sp_trace_setevent @TraceID, 24, 32, @on
28 exec sp_trace_setevent @TraceID, 24, 56, @on
29 exec sp_trace_setevent @TraceID, 24, 64, @on
30 exec sp_trace_setevent @TraceID, 24, 1, @on
31 exec sp_trace_setevent @TraceID, 24, 9, @on
32 exec sp_trace_setevent @TraceID, 24, 41, @on
33 exec sp_trace_setevent @TraceID, 24, 49, @on
34 exec sp_trace_setevent @TraceID, 24, 57, @on
35 exec sp_trace_setevent @TraceID, 24, 2, @on
36 exec sp_trace_setevent @TraceID, 24, 6, @on
37 exec sp_trace_setevent @TraceID, 24, 10, @on
38 exec sp_trace_setevent @TraceID, 24, 14, @on
39 exec sp_trace_setevent @TraceID, 24, 22, @on
40 exec sp_trace_setevent @TraceID, 24, 26, @on
41 exec sp_trace_setevent @TraceID, 24, 58, @on
42 exec sp_trace_setevent @TraceID, 24, 3, @on
43 exec sp_trace_setevent @TraceID, 24, 11, @on
44 exec sp_trace_setevent @TraceID, 24, 51, @on
45 exec sp_trace_setevent @TraceID, 24, 4, @on
46 exec sp_trace_setevent @TraceID, 24, 12, @on
47 exec sp_trace_setevent @TraceID, 24, 52, @on
48 exec sp_trace_setevent @TraceID, 24, 60, @on
49 exec sp_trace_setevent @TraceID, 24, 13, @on
50 exec sp_trace_setevent @TraceID, 25, 7, @on
51 exec sp_trace_setevent @TraceID, 25, 15, @on
52 exec sp_trace_setevent @TraceID, 25, 55, @on
53 exec sp_trace_setevent @TraceID, 25, 8, @on
54 exec sp_trace_setevent @TraceID, 25, 32, @on
55 exec sp_trace_setevent @TraceID, 25, 56, @on
56 exec sp_trace_setevent @TraceID, 25, 64, @on
57 exec sp_trace_setevent @TraceID, 25, 1, @on
58 exec sp_trace_setevent @TraceID, 25, 9, @on
59 exec sp_trace_setevent @TraceID, 25, 25, @on
60 exec sp_trace_setevent @TraceID, 25, 41, @on
61 exec sp_trace_setevent @TraceID, 25, 49, @on
62 exec sp_trace_setevent @TraceID, 25, 57, @on
63 exec sp_trace_setevent @TraceID, 25, 2, @on
64 exec sp_trace_setevent @TraceID, 25, 10, @on
65 exec sp_trace_setevent @TraceID, 25, 26, @on
66 exec sp_trace_setevent @TraceID, 25, 58, @on
67 exec sp_trace_setevent @TraceID, 25, 3, @on
68 exec sp_trace_setevent @TraceID, 25, 11, @on
69 exec sp_trace_setevent @TraceID, 25, 35, @on
70 exec sp_trace_setevent @TraceID, 25, 51, @on
71 exec sp_trace_setevent @TraceID, 25, 4, @on
72 exec sp_trace_setevent @TraceID, 25, 12, @on
73 exec sp_trace_setevent @TraceID, 25, 52, @on
74 exec sp_trace_setevent @TraceID, 25, 60, @on
75 exec sp_trace_setevent @TraceID, 25, 13, @on
76 exec sp_trace_setevent @TraceID, 25, 6, @on
77 exec sp_trace_setevent @TraceID, 25, 14, @on
78 exec sp_trace_setevent @TraceID, 25, 22, @on
79 exec sp_trace_setevent @TraceID, 23, 7, @on
80 exec sp_trace_setevent @TraceID, 23, 55, @on
81 exec sp_trace_setevent @TraceID, 23, 8, @on
82 exec sp_trace_setevent @TraceID, 23, 32, @on
83 exec sp_trace_setevent @TraceID, 23, 56, @on
84 exec sp_trace_setevent @TraceID, 23, 64, @on
85 exec sp_trace_setevent @TraceID, 23, 1, @on
86 exec sp_trace_setevent @TraceID, 23, 9, @on
87 exec sp_trace_setevent @TraceID, 23, 41, @on
88 exec sp_trace_setevent @TraceID, 23, 49, @on
89 exec sp_trace_setevent @TraceID, 23, 57, @on
90 exec sp_trace_setevent @TraceID, 23, 2, @on
91 exec sp_trace_setevent @TraceID, 23, 6, @on
92 exec sp_trace_setevent @TraceID, 23, 10, @on
93 exec sp_trace_setevent @TraceID, 23, 14, @on
94 exec sp_trace_setevent @TraceID, 23, 22, @on
95 exec sp_trace_setevent @TraceID, 23, 26, @on
96 exec sp_trace_setevent @TraceID, 23, 58, @on
97 exec sp_trace_setevent @TraceID, 23, 3, @on
98 exec sp_trace_setevent @TraceID, 23, 11, @on
99 exec sp_trace_setevent @TraceID, 23, 51, @on
100 exec sp_trace_setevent @TraceID, 23, 4, @on
101 exec sp_trace_setevent @TraceID, 23, 12, @on
102 exec sp_trace_setevent @TraceID, 23, 52, @on
103 exec sp_trace_setevent @TraceID, 23, 60, @on
104
105 --上面这里是设置收取什么事件，以及他们的哪些数据字段
106 --如果选的事件比较多，会很长，不用去修改他们
107
108 -- Set the Filters
109 declare @intfilter int
110 declare @bigintfilter bigint
111
112 exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - dd744289-3250-498c-b913-226073c75507'
113 --这里是设置过滤条件，也不用修改
114 -- Set the trace status to start
115 exec sp_trace_setstatus @TraceID, 1
116 --运行了这句话，Trace就被开启了
117
118 -- display trace id for future references
119 select TraceID=@TraceID
120 --这句话会返回一个数字，也就是这个Server Side Trace的编号
121 --因为一个SQL可以开启多个Trace
122 --一定要记录下这个编号，关闭Trace的时候要使用他
123 goto finish
124
125 error:
126 select ErrorCode=@rc
127
128 finish:
129 go
130
131 --如果要关闭这个Trace，要运行下面的两句话
132 EXEC [sys].[sp_trace_setstatus] <traceid>,0
133 GO
134 --停止trace
135
136 EXEC [sys].[sp_trace_setstatus] <traceid>,2
137 GO
138 --完全关闭trace，并且删除这个定义
139
140 --这里的<traceid>就是刚才select TraceID=@TraceID 得到的那个数字。
141 --如果不关闭，SQL会不停地生成Trace文件，直到服务重启，这也是很危险的
142 --系统存储过程还有一些扩展的功能。
