teach you to use T-SQL statements to establish a boot trace

In general, we use Profiler to track the operation of SQL Server, and sometimes we need to do some regular tracking to check the health of SQL Server for a certain period of time, because profiler does not support scheduling, so you should find a way to For us to start a trace by setting up a job, this article describes the use of T-SQL statements to enable tracing to suit this requirement.

Example of starting with a script and setting up a trace

With this example, you can learn about the stored procedures involved in using scripts to track, and to understand the meaning of the specific syntax and parameters of these stored procedures, check the online Help

/****************************************************/
/* Created by:sql Profiler */
/* Date : 2004/06/19 16:50:05 */
/****************************************************/
--Create a Queue
Declare @rc int
Declare @TraceID int
Declare @maxfilesize bigint
Set @maxfilesize = 5
--Please replace the text I Nsertfilenamehere, with the appropriate
-filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The. trc extension
--would be appended to the filename automatically. If you are are writing from
--remote server to local drive, "use UNC path and make sure server has
--write ACC ESS to your network share
exec @rc = sp_trace_create @TraceID output, 0, N ' c:\test ', @maxfilesize, NULL
if (@rc!= 0 goto Error
--Client side File and Table cannot be scripted
--writing to a Table are not supported through the SP ' s
--Set The events
declare @on bit
Set @on = 1
exec sp_trace_setevent @Traceid, 1, @on
exec sp_trace_setevent @TraceID, @on
exec sp_trace_setevent @TraceID, @on
--Set The Filters
declare @intfilter int
Declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0 , 7, N ' SQL Profiler '
Set @intfilter =
Exec sp_trace_setfilter @TraceID, 0, 4, @intfilter
Set @intfilte R = 1
Exec sp_trace_setfilter @TraceID, 1, 0, @intfilter
exec sp_trace_setfilter @TraceID, 1, 6, N ' pubs ' --Set The trace status to start
exec sp_trace_setstatus @TraceID, 1
--Display trace ID for future references< br> Select traceid= @TraceID
Goto finish
Error:
Select errorcode= @rc
Finish:
Go

Second, the simplest form of generating trace scripts

Profiler establishes traces and sets various options to run the trace after completion

Then generate the script:

Profiler--FILE--Export trace-defined files-select the appropriate version.

This will generate a tracking script, open the generated script, and modify the inside:

exec @rc = sp_trace_create

Section to set the save file for the trace result (the trace result can only be saved to the file when the phrase is tracked)

Then, when you need to track, run this script to start the trace

After the trace is started, tracing is done automatically, so you can close the Query Analyzer to do something else.

Iii. Known Issues

1. Trace records are not written to trace files in real time, so it may be that the trace information is written to the trace file when you stop tracing.

2. View the currently available traces (see the online Help for an explanation of the result set):

SELECT * FROM ::fn_trace_getinfo(0)

3. Stop a trace, you can set the automatic stop time in the sp_trace_create statement, or you can manually stop the trace, using the following statement:

EXEC sp_trace_setstatus
　　@traceid = 1 ,　 -- 跟踪的id
　　@status = 0　　　-- 停止, 这样以后还可能指定此项为来启用
EXEC sp_trace_setstatus
　　@traceid = 1 ,
　　@status = 2　　　-- 关闭, 彻底释放