trace跟踪代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】 trace跟踪
作者:Dezai.CN / 发布于2011/11/22/ 533
<div>
<p class="MsoNormal" align="left"><span lang="EN-US">------------------------------------------------</span>
<p class="MsoNormal" align="left"><span lang="EN-US">--</span><span>功能：</span><span lang="EN-US">trace</span><span>跟踪</span>
<p class="MsoNormal" align="left"><span lang="EN-US">--</span><span>时间：年月日</span>
<p class="MsoNormal" align="left"><span lang="EN-US">-------------------------------------------------</span>
<p class="MsoNormal" align="left">
<p class="MsoNormal" align="left">
<p class="MsoNormal" align="left"><span lang="EN-US">SET</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> </span><span>NOCOUNT</span><span style="color: rgb(0,0,0)"> </span><span>ON</span><span>;</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">USE</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> master</span><span>;</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span style="color: rgb(0,0,0)">GO</span></span>
<p class="MsoNormal" align="left">
<p class="MsoNormal" align="left"><span lang="EN-US">IF</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> </span><span>OBJECT_ID</span><span>(</span><span>'dbo.sp_perfworkload_trace_start'</span><span>)</span><span style="color: rgb(0,0,0)"> </span><span>IS</span><span style="color: rgb(0,0,0)"> </span><span>NOT</span><span style="color: rgb(0,0,0)"> </span><span>NULL</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>DROP</span><span style="color: rgb(0,0,0)"> </span><span>PROC</span><span style="color: rgb(0,0,0)"> dbo</span><span>.</span><span style="color: rgb(0,0,0)">sp_perfworkload_trace_start</span><span>;</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span style="color: rgb(0,0,0)">GO</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">-- Creation script for the sp_perfworkload_trace_start stored procedure</span>
<p class="MsoNormal" align="left"><span lang="EN-US">CREATE</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> </span><span>PROC</span><span style="color: rgb(0,0,0)"> dbo</span><span>.</span><span style="color: rgb(0,0,0)">sp_perfworkload_trace_start</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span style="color: rgb(0,0,0)">@dbid<span> </span></span><span>AS</span><span style="color: rgb(0,0,0)"> </span><span>INT</span><span>,</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span style="color: rgb(0,0,0)">@tracefile </span><span>AS</span><span style="color: rgb(0,0,0)"> </span><span>NVARCHAR</span><span>(</span><span style="color: rgb(0,0,0)">254</span><span>),</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span style="color: rgb(0,0,0)">@traceid<span> </span></span><span>AS</span><span style="color: rgb(0,0,0)"> </span><span>INT</span><span style="color: rgb(0,0,0)"> </span><span>OUTPUT</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">AS</span>
<p class="MsoNormal" align="left"><span lang="EN-US">-- Create a Queue</span>
<p class="MsoNormal" align="left"><span lang="EN-US">DECLARE</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> @rc<span> </span></span><span>AS</span><span style="color: rgb(0,0,0)"> </span><span>INT</span><span>;</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">DECLARE</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> @maxfilesize </span><span>AS</span><span style="color: rgb(0,0,0)"> </span><span>BIGINT</span><span>;</span></span>
<p class="MsoNormal" align="left">
<p class="MsoNormal" align="left"><span lang="EN-US">SET</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> @maxfilesize </span><span>=</span><span style="color: rgb(0,0,0)"> 5</span><span>;</span></span>
<p class="MsoNormal" align="left">
<p class="MsoNormal" align="left"><span lang="EN-US">EXEC</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> @rc </span><span>=</span><span style="color: rgb(0,0,0)"> sp_trace_create @traceid </span><span>OUTPUT</span><span>,</span><span style="color: rgb(0,0,0)"> 0</span><span>,</span><span style="color: rgb(0,0,0)"> @tracefile</span><span>,</span><span style="color: rgb(0,0,0)"> @maxfilesize</span><span>,</span><span style="color: rgb(0,0,0)"> </span><span>NULL</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">IF</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> </span><span>(</span><span style="color: rgb(0,0,0)">@rc </span><span>!=</span><span style="color: rgb(0,0,0)"> 0</span><span>)</span><span style="color: rgb(0,0,0)"> </span><span>GOTO</span><span style="color: rgb(0,0,0)"> error</span><span>;</span></span>
<p class="MsoNormal" align="left">
<p class="MsoNormal" align="left"><span lang="EN-US">-- Client side File and Table cannot be scripted</span>
<p class="MsoNormal" align="left">
<p class="MsoNormal" align="left"><span lang="EN-US">-- Set the events</span>
<p class="MsoNormal" align="left"><span lang="EN-US">DECLARE</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> @on </span><span>AS</span><span style="color: rgb(0,0,0)"> </span><span>BIT</span><span>;</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">SET</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> @on </span><span>=</span><span style="color: rgb(0,0,0)"> 1</span><span>;</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">exec</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setevent @TraceID</span><span>,</span><span style="color: rgb(0,0,0)"> 10</span><span>,</span><span style="color: rgb(0,0,0)"> 1</span><span>,</span><span style="color: rgb(0,0,0)"> @on</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">exec</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setevent @TraceID</span><span>,</span><span style="color: rgb(0,0,0)"> 10</span><span>,</span><span style="color: rgb(0,0,0)"> 3</span><span>,</span><span style="color: rgb(0,0,0)"> @on</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">exec</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setevent @TraceID</span><span>,</span><span style="color: rgb(0,0,0)"> 10</span><span>,</span><span style="color: rgb(0,0,0)"> 11</span><span>,</span><span style="color: rgb(0,0,0)"> @on</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">exec</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setevent @TraceID</span><span>,</span><span style="color: rgb(0,0,0)"> 10</span><span>,</span><span style="color: rgb(0,0,0)"> 35</span><span>,</span><span style="color: rgb(0,0,0)"> @on</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">exec</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setevent @TraceID</span><span>,</span><span style="color: rgb(0,0,0)"> 10</span><span>,</span><span style="color: rgb(0,0,0)"> 12</span><span>,</span><span style="color: rgb(0,0,0)"> @on</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">exec</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setevent @TraceID</span><span>,</span><span style="color: rgb(0,0,0)"> 10</span><span>,</span><span style="color: rgb(0,0,0)"> 13</span><span>,</span><span style="color: rgb(0,0,0)"> @on</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">exec</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setevent @TraceID</span><span>,</span><span style="color: rgb(0,0,0)"> 43</span><span>,</span><span style="color: rgb(0,0,0)"> 1</span><span>,</span><span style="color: rgb(0,0,0)"> @on</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">exec</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setevent @TraceID</span><span>,</span><span style="color: rgb(0,0,0)"> 43</span><span>,</span><span style="color: rgb(0,0,0)"> 3</span><span>,</span><span style="color: rgb(0,0,0)"> @on</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">exec</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setevent @TraceID</span><span>,</span><span style="color: rgb(0,0,0)"> 43</span><span>,</span><span style="color: rgb(0,0,0)"> 11</span><span>,</span><span style="color: rgb(0,0,0)"> @on</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">exec</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setevent @TraceID</span><span>,</span><span style="color: rgb(0,0,0)"> 43</span><span>,</span><span style="color: rgb(0,0,0)"> 35</span><span>,</span><span style="color: rgb(0,0,0)"> @on</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">exec</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setevent @TraceID</span><span>,</span><span style="color: rgb(0,0,0)"> 43</span><span>,</span><span style="color: rgb(0,0,0)"> 12</span><span>,</span><span style="color: rgb(0,0,0)"> @on</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">exec</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setevent @TraceID</span><span>,</span><span style="color: rgb(0,0,0)"> 43</span><span>,</span><span style="color: rgb(0,0,0)"> 28</span><span>,</span><span style="color: rgb(0,0,0)"> @on</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">exec</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setevent @TraceID</span><span>,</span><span style="color: rgb(0,0,0)"> 43</span><span>,</span><span style="color: rgb(0,0,0)"> 13</span><span>,</span><span style="color: rgb(0,0,0)"> @on</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">exec</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setevent @TraceID</span><span>,</span><span style="color: rgb(0,0,0)"> 45</span><span>,</span><span style="color: rgb(0,0,0)"> 1</span><span>,</span><span style="color: rgb(0,0,0)"> @on</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">exec</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setevent @TraceID</span><span>,</span><span style="color: rgb(0,0,0)"> 45</span><span>,</span><span style="color: rgb(0,0,0)"> 3</span><span>,</span><span style="color: rgb(0,0,0)"> @on</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">exec</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setevent @TraceID</span><span>,</span><span style="color: rgb(0,0,0)"> 45</span><span>,</span><span style="color: rgb(0,0,0)"> 11</span><span>,</span><span style="color: rgb(0,0,0)"> @on</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">exec</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setevent @TraceID</span><span>,</span><span style="color: rgb(0,0,0)"> 45</span><span>,</span><span style="color: rgb(0,0,0)"> 35</span><span>,</span><span style="color: rgb(0,0,0)"> @on</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">exec</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setevent @TraceID</span><span>,</span><span style="color: rgb(0,0,0)"> 45</span><span>,</span><span style="color: rgb(0,0,0)"> 12</span><span>,</span><span style="color: rgb(0,0,0)"> @on</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">exec</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setevent @TraceID</span><span>,</span><span style="color: rgb(0,0,0)"> 45</span><span>,</span><span style="color: rgb(0,0,0)"> 28</span><span>,</span><span style="color: rgb(0,0,0)"> @on</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">exec</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setevent @TraceID</span><span>,</span><span style="color: rgb(0,0,0)"> 45</span><span>,</span><span style="color: rgb(0,0,0)"> 13</span><span>,</span><span style="color: rgb(0,0,0)"> @on</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">exec</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setevent @TraceID</span><span>,</span><span style="color: rgb(0,0,0)"> 12</span><span>,</span><span style="color: rgb(0,0,0)"> 1</span><span>,</span><span style="color: rgb(0,0,0)"> @on</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">exec</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setevent @TraceID</span><span>,</span><span style="color: rgb(0,0,0)"> 12</span><span>,</span><span style="color: rgb(0,0,0)"> 3</span><span>,</span><span style="color: rgb(0,0,0)"> @on</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">exec</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setevent @TraceID</span><span>,</span><span style="color: rgb(0,0,0)"> 12</span><span>,</span><span style="color: rgb(0,0,0)"> 11</span><span>,</span><span style="color: rgb(0,0,0)"> @on</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">exec</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setevent @TraceID</span><span>,</span><span style="color: rgb(0,0,0)"> 12</span><span>,</span><span style="color: rgb(0,0,0)"> 35</span><span>,</span><span style="color: rgb(0,0,0)"> @on</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">exec</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setevent @TraceID</span><span>,</span><span style="color: rgb(0,0,0)"> 12</span><span>,</span><span style="color: rgb(0,0,0)"> 12</span><span>,</span><span style="color: rgb(0,0,0)"> @on</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">exec</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setevent @TraceID</span><span>,</span><span style="color: rgb(0,0,0)"> 12</span><span>,</span><span style="color: rgb(0,0,0)"> 13</span><span>,</span><span style="color: rgb(0,0,0)"> @on</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">exec</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setevent @TraceID</span><span>,</span><span style="color: rgb(0,0,0)"> 41</span><span>,</span><span style="color: rgb(0,0,0)"> 1</span><span>,</span><span style="color: rgb(0,0,0)"> @on</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">exec</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setevent @TraceID</span><span>,</span><span style="color: rgb(0,0,0)"> 41</span><span>,</span><span style="color: rgb(0,0,0)"> 3</span><span>,</span><span style="color: rgb(0,0,0)"> @on</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">exec</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setevent @TraceID</span><span>,</span><span style="color: rgb(0,0,0)"> 41</span><span>,</span><span style="color: rgb(0,0,0)"> 11</span><span>,</span><span style="color: rgb(0,0,0)"> @on</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">exec</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setevent @TraceID</span><span>,</span><span style="color: rgb(0,0,0)"> 41</span><span>,</span><span style="color: rgb(0,0,0)"> 35</span><span>,</span><span style="color: rgb(0,0,0)"> @on</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">exec</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setevent @TraceID</span><span>,</span><span style="color: rgb(0,0,0)"> 41</span><span>,</span><span style="color: rgb(0,0,0)"> 12</span><span>,</span><span style="color: rgb(0,0,0)"> @on</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">exec</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setevent @TraceID</span><span>,</span><span style="color: rgb(0,0,0)"> 41</span><span>,</span><span style="color: rgb(0,0,0)"> 13</span><span>,</span><span style="color: rgb(0,0,0)"> @on</span></span>
<p class="MsoNormal" align="left">
<p class="MsoNormal" align="left"><span lang="EN-US">-- Set the Filters</span>
<p class="MsoNormal" align="left"><span lang="EN-US">DECLARE</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> @intfilter </span><span>AS</span><span style="color: rgb(0,0,0)"> </span><span>INT</span><span>;</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">DECLARE</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> @bigintfilter </span><span>AS</span><span style="color: rgb(0,0,0)"> </span><span>BIGINT</span><span>;</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">-- Application name filter</span>
<p class="MsoNormal" align="left"><span lang="EN-US">EXEC</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setfilter @traceid</span><span>,</span><span style="color: rgb(0,0,0)"> 10</span><span>,</span><span style="color: rgb(0,0,0)"> 0</span><span>,</span><span style="color: rgb(0,0,0)"> 7</span><span>,</span><span style="color: rgb(0,0,0)"> N</span><span>'SQL Server Profiler%'</span><span>;</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">-- Database ID filter</span>
<p class="MsoNormal" align="left"><span lang="EN-US">EXEC</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setfilter @traceid</span><span>,</span><span style="color: rgb(0,0,0)"> 3</span><span>,</span><span style="color: rgb(0,0,0)"> 0</span><span>,</span><span style="color: rgb(0,0,0)"> 0</span><span>,</span><span style="color: rgb(0,0,0)"> @dbid</span><span>;</span></span>
<p class="MsoNormal" align="left">
<p class="MsoNormal" align="left"><span lang="EN-US">-- Set the trace status to start</span>
<p class="MsoNormal" align="left"><span lang="EN-US">EXEC</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> sp_trace_setstatus @traceid</span><span>,</span><span style="color: rgb(0,0,0)"> 1</span><span>;</span></span>
<p class="MsoNormal" align="left">
<p class="MsoNormal" align="left"><span lang="EN-US">-- Print trace id and file name for future references</span>
<p class="MsoNormal" align="left"><span lang="EN-US">PRINT</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> </span><span>'Trce ID: '</span><span style="color: rgb(0,0,0)"> </span><span>+</span><span style="color: rgb(0,0,0)"> </span><span>CAST</span><span>(</span><span style="color: rgb(0,0,0)">@traceid </span><span>AS</span><span style="color: rgb(0,0,0)"> </span><span>VARCHAR</span><span>(</span><span style="color: rgb(0,0,0)">10</span><span>))</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>+</span><span style="color: rgb(0,0,0)"> </span><span>', Trace File: '''</span><span style="color: rgb(0,0,0)"> </span><span>+</span><span style="color: rgb(0,0,0)"> @tracefile </span><span>+</span><span style="color: rgb(0,0,0)"> </span><span>''''</span><span>;</span></span>
<p class="MsoNormal" align="left">
<p class="MsoNormal" align="left"><span lang="EN-US">GOTO</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> finish</span><span>;</span></span>
<p class="MsoNormal" align="left">
<p class="MsoNormal" align="left"><span lang="EN-US"><span style="color: rgb(0,0,0)">error</span><span>:</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">PRINT</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> </span><span>'Error Code: '</span><span style="color: rgb(0,0,0)"> </span><span>+</span><span style="color: rgb(0,0,0)"> </span><span>CAST</span><span>(</span><span style="color: rgb(0,0,0)">@rc </span><span>AS</span><span style="color: rgb(0,0,0)"> </span><span>VARCHAR</span><span>(</span><span style="color: rgb(0,0,0)">10</span><span>));</span></span>
<p class="MsoNormal" align="left">
<p class="MsoNormal" align="left"><span lang="EN-US"><span style="color: rgb(0,0,0)">finish</span><span>:</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span style="color: rgb(0,0,0)">GO</span></span>
</div>
试试其它关键字
　trace跟踪　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)