How to Use T-SQL statements to establish startup Tracing

Generally, we use an event probe to track the running status of SQL Server. Sometimes we also need to regularly track the running status of SQL Server within a certain period of time, because the event probe does not support scheduling, you should look for a method that is suitable for starting a trail by setting the job, this article describes the use of T-SQL statements to enable the trail is suitable for this demand.

　　1. Use a script to start and Set Tracing examples

Through this example, you can understand the stored procedures involved in tracking with scripts. To learn the specific syntax and parameter meanings of these stored procedures, please query the online help

/*************************************** * ************ // * Created: SQL profiler * // * Date: 16:50:05 *//*********************************** * ***************/-- create a queue declare @ RC int declare @ traceid int declare @ maxfilesize bigint set @ maxfilesize = 5 -- please replace the text insertfilenamehere, with an appropriate -- filename prefixed by a path, e.g ., c:/myfolder/mytrace. The. TRC extension -- will be appended to the filename automatically. if you are writing from -- remote server to local drive, please use UNC path and make sure server has -- write access 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 is not supported through the SP's -- set the events declare @ on bit set @ on = 1 exec sp_trace_setevent @ traceid, 12, 1, @ on exec sp_trace_setevent @ traceid, 12, 12, @ on exec sp_trace_setevent @ traceid, 12, 14, @ on -- set the filters declare @ intfilter int declare @ bigintfilter bigint exec sp_trace_setfilter @ traceid, 10, 0, 7, N 'SQL profiler' set @ intfilter = 100 exec sp_trace_setfilter @ traceid, 22, 0, 4, @ intfilter set @ intfilter = 1 exec sp_trace_setfilter @ traceid, 23, 1, 0, @ intfilter exec sp_trace_setfilter @ traceid, 35, 1, 6, N 'pubs' -- set the trace status to start exec sp_trace_setstatus @ traceid, 1 -- display trace ID for future references select traceid = @ traceid goto finish error: Select errorcode = @ RC finish: go

 

　　Ii. Generate the simplest tracking script

The event probe creates a trail and sets various options to run the trail after completion.

Then generate the script:

Event probe -- file -- export the file defined by the trail -- select the appropriate version.

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

Exec @ rc = sp_trace_create

To save the trace results. (The trace results can only be saved to files when the statement is used for tracking)

Then, run this script to start the trail when you need to trace it.

After tracing is started, tracing is automatically performed, so you can disable the query analyzer to do other things.

　　Iii. Known issues

1. The tracking record is not written to the tracking file in real time. Therefore, it may be written to the tracking file only when you stop tracking.

2. Check that the current trail is available (for more information about the result set, see online help ):

Select * From: fn_trace_getinfo (0)

3. Stop a trail. You can set the automatic stop time in the sp_trace_create statement or stop the trail manually. Use the following statement:

Exec sp_trace_setstatus @ traceid = 1, -- trace ID @ status = 0 -- stop, this item may be specified later to enable exec sp_trace_setstatus @ traceid = 1, @ status = 2 -- close and release permanently