笔记53-徐 跟踪死锁和释放死锁的SQL脚步

笔记53-徐 跟踪死锁和释放死锁的SQL脚步

  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 --系统存储过程还有一些扩展的功能。

 

posted @ 2013-07-29 08:47  桦仔  阅读(518)  评论(0)    收藏  举报