THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

Use Trace to capture Production Server Activity

One of the great things about SQL Server is that there are many ways to accomplish the same task.

When problems arise it can be difficult to determine what went wrong, so I keep a trace going on all my production servers. I capture details on all RPC Completed and Batch Completed events, so I know what was submitted to the server for processing and when, so when an application problem arises I can see what the application sent to SQL Server, and can work through the issue with the developers or vendor (for third-party apps).

Now, SQL Server Profiler has a great graphical interface that allows you to select what information to trace, but it can seriously impact performance and on a production server that can be a very bad thing. Profiler has a great option that allows you to script the trace commands to a script file. You can then run the script, and you get the same trace you would get without incurring the performance overhead you'd get by running Profiler.

First, set up the trace you want to run in Profiler. You'll want to select the Save To File option, and specify a file name on the server hard drive. (I use a subdirectory under my Backup directory, so the nightly backup to tape grabs the trace files as well.) The default file size is 5MB, but I set it at 20MB so I don't get too many physical files, but I do set it to roll over so I don't lose any trace data.

Once you've set up the options you want you can go to the File menu, select Export, then Script Trace Definition, then For SQL Server 2005, and specify a file name for the script. It'll produce a file something like this:


/* Created by: SQL Server Profiler 2005 */
/* Date: 08/28/2007 08:41:47 AM */

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 20

-- 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'InsertFileNameHere', @maxfilesize, NULL
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - fab17603-e5cc-4107-80a1-65330cd2eece'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

select ErrorCode=@rc



Read the comments, and set up the file name for the trace file appropriately. You can close Profiler because you no longer need it. You can run the script in a new query in SQL Server Management Studio, or you can set up a job to start it up. Once it's running it'll save the server activity you selected earlier in a trace file, which you can then review after something unusual has occurred to see what was sent to the server.


Published Tuesday, August 28, 2007 9:17 AM by AllenMWhite



Linchi Shea said:

What is not commonly known is that the generated SQL trace may not behave the same as the SQL Profiler trace in the way the filter conditions are interpreted. Ouch!

August 28, 2007 9:48 AM

AllenMWhite said:

Linchi, can you give me some examples?  I've never run into a problem getting the results I expected.

August 28, 2007 12:25 PM

Linchi Shea said:

The SQL Trace definition script generated by SQL Profiler GUI may not be the same as the one actually used by SQL Profiler. Here's how to reproduce the problem:

* Open SQL2005 SQL Profiler, and click on File | New Trace.

* Select events RPC:Completed and SQL:BatchCompleted, and add two filter conditions on the LoginName column to filter for sa and %_FC (i.e. like 'sa' and '%_FC')

* Login as abc using Query Analyzer to the SQL2005 instance. This login does NOT meet the filter condition, and therefore its activities should not be traced, and are indeed not traced. (So far so good.)

* Now, click File | Export | Script Trace Definition to a SQL2005 script, and save the script as SQLprofiler_Generated_script.sql (file attached)

* Run this script to start a SQL Trace.

* Now, login into the SQL2005 instance as abc, and stop the trace.

* Open the trace file in SQL Profiler, and you would see the activities of the login abc are recorded in the trace, when its activities should not have been traced.

Note if you examine the difference between the generated trace definition script and the SQL statements sent by SQL Profiler, you would notice that the only difference is one of the filter setting statements.

SQL Profiler sent the following call:

   exec sp_trace_setfilter 2,11,0,6,N'sa'

The generated trace definition script has the following:

   exec sp_trace_setfilter @TraceID, 11, 1, 6, N'sa'

August 28, 2007 1:33 PM

AllenMWhite said:

Got it.  Thanks, Linchi!

August 28, 2007 2:51 PM

Uri Dimant said:

Hi Linchi and Allen

I'm sure you also noticed  that in generated script you need to modify quotation for Login Parameter as it did not create it properly

exec sp_trace_setfilter @TraceID, 11, 1, 6, N''sa' and '%_FC''

August 29, 2007 12:12 AM

ALZDBA said:

needs it to be mentioned that you can load your tracefile(s) into a table using :


INTO TRC_123_50

FROM ::fn_trace_gettable('X:\MSSQL.1\MSSQL\LOG\log__50.trc', DEFAULT)


Nice job ;)

August 31, 2007 1:47 PM

VA_SQL said:

Hi All,

I have a script and it's ready to go, but i need to create a job so that it executes the script and runs the trace for a week and every Monday at 10 am I have to send that trace file to my manager, I have already created a dts pkg to convert that .trc file to xls and e-mail automatically to my manager.

I am having problem creating job and schedule it to start at every monday at 10 am to run the trace and stop it next monday at 9:55 and that;s when it kicks off that DTS to mail last weeks trace xls.

and again start the job at 10 am.

I really appreciate your help!


October 17, 2007 11:22 AM

ShanDBA said:

I have setup a SQL trace for auditing purposes on SQL 2000 using SQL:Batchcompleted to capture activities. I have noticed that I also get bunch of information on my TextData that is internal to either Query Analyzer or the Enterprise Manager. Is there a way to filter system related records from the trace? For example, If i opened Qry Analyzer, I get "set quoted_identifier off" and "SET TEXTSIZE 64512" in the TextData column. any help will be appreciated.


September 28, 2009 3:00 PM
New Comments to this post are disabled

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog


Privacy Statement