Data Management

Write custom trace files in TSQL

SQL Server 2005's default trace is great for monitoring system information and for finding out what happened on your server after problems occur. However, there are times when the events that the default captures are not what you need. Here are instructions for how you can create your own trace files in TSQL to catch events on your database machine.

SQL Server 2005's default trace is great for monitoring system information and for finding out what happened on your server after problems occur. However, there are times when the events that the default captures are not what you need. Here are instructions for how you can create your own trace files in TSQL to catch events on your database machine.

SQL Server Profiler

Before explaining how to write a custom trace in TSQL, I will briefly discuss SQL Server Profiler. Profiler is a user interface that graphically enables you to create SQL Server traces to monitor events on your server. This is a great tool for monitoring database events and, in many ways, it is preferable to writing custom trace scripts to monitor your server if you are planning on monitoring for a short period of time. The user interface is simple to use, which makes it very fast for creating a trace.

TSQL traces

One of the main benefits of using a custom trace written in TSQL is that it allows you to quickly tweak the trace settings; it also allows you to run the traces without a user interface running in the background. This is great for scheduling traces to run at certain periods of time.

Typically with SQL Profiler, a trace is created, and events are captured for a limited time. You can start a trace in SQL Profiler and exit the program and keep the trace running, but I do not recommend it. If you are going to monitor events for a long period of time (i.e., typically more than a couple of days), or at times when you are not sitting at your PC to start a trace, it is preferable to run the trace on the server in TSQL; or you can create a stored procedure that can be executed when the server starts to ensure that your trace is always running.

Trace example

A SQL Server trace watches for defined events that occur in the database engine. Each event has certain data associated with it that defines what has occurred in the event. That is essentially all that is involved in defining a trace — events and data columns.

To create the trace, you use the system stored procedure sp_trace_create, which takes a set of parameters such as the location of the file where the trace data will be stored, the maximum file size, an option value, and outputs the ID value of the trace that is created. Here is the script to create the trace:

DECLARE @TraceID INT

DECLARE @MaxSize bigint

SET @MaxSize = 15

EXECUTE sp_trace_create @TraceID output,

@options = 2,  —//rollover the file when max size is reached

@tracefile = N'C:Tracefile.trc', —//trace file name

@maxfilesize  = @MaxSize —//maximum file size

SELECT @TraceID  —//output the traceid create for viewing

The above procedure creates the trace on the server. For this trace to be functional, I need to attach the events and data columns to it that I want to monitor. There is only one stored procedure that I will need to call to attach the data. This is where some confusion can occur. Events and data columns are passed into the stored procedure as separate paramereters and as numerical values — some care will need to be taken when defining these. This Microsoft article offers a complete listing of the events and associated data columns.

In this example, I want to monitor any time a stored procedure is executed remotely, or when any TSQL batch is executed; these events and columns are 10 and 12, respectively. As for the data columns, I am interested in when the call is made and when it finishes; how long the call takes to execute; where it executes, and what is executing. The data columns I have defined below will catch this information:

DECLARE @On BIT

SET @On = 1

EXECUTE sp_trace_setevent @TraceID, 10, 15, @On

EXECUTE sp_trace_setevent @TraceID, 10, 16, @On

EXECUTE sp_trace_setevent @TraceID, 10, 1, @On

EXECUTE sp_trace_setevent @TraceID, 10, 9, @On

EXECUTE sp_trace_setevent @TraceID, 10, 17, @On

EXECUTE sp_trace_setevent @TraceID, 10, 6, @On

EXECUTE sp_trace_setevent @TraceID, 10, 10, @On

EXECUTE sp_trace_setevent @TraceID, 10, 14, @On

EXECUTE sp_trace_setevent @TraceID, 10, 18, @On

EXECUTE sp_trace_setevent @TraceID, 10, 11, @On

EXECUTE sp_trace_setevent @TraceID, 10, 12, @On

EXECUTE sp_trace_setevent @TraceID, 10, 13, @On

EXECUTE sp_trace_setevent @TraceID, 12, 15, @On

EXECUTE sp_trace_setevent @TraceID, 12, 16, @On

EXECUTE sp_trace_setevent @TraceID, 12, 1, @On

EXECUTE sp_trace_setevent @TraceID, 12, 9, @On

EXECUTE sp_trace_setevent @TraceID, 12, 17, @On

EXECUTE sp_trace_setevent @TraceID, 12, 6, @On

EXECUTE sp_trace_setevent @TraceID, 12, 10, @On

EXECUTE sp_trace_setevent @TraceID, 12, 14, @On

EXECUTE sp_trace_setevent @TraceID, 12, 18, @On

EXECUTE sp_trace_setevent @TraceID, 12, 11, @On

EXECUTE sp_trace_setevent @TraceID, 12, 12, @On

EXECUTE sp_trace_setevent @TraceID, 12, 13, @On
(Note: This would be a good time to define any trace filters if I wanted to create them. A trace filter allows me to only catch events with certain values in the data columns, or events that exclude certain events in the data columns. This is great if you don't want to see commonly executed events, or if you are only interested in the execution of a certain stored procedure. I will not be setting any filters in today's example.)

Now I can start the trace. To turn the trace on, I make a call to the system stored procedure sp_trace_setstatus. The procedure accepts the ID of the trace, which I received above, and a status of 1, which turns the trace on.

EXECUTE sp_trace_setstatus @TraceID, 1

I can verify that the trace is running properly with a couple of system function calls. To view all of the traces running in the system, run the following:

SELECT * FROM fn_trace_getinfo(0)

Based on the TraceID that I just created, I can see that the trace I just created is the second trace running on my system. If I wanted to view the data in this trace file without opening the physical file on the file system, I can use the following trace function, which returns the contents of the file in a tabular format:

SELECT * FROM fn_trace_gettable('C:Tracefile.trc',1)

Conclusion

While I use SQL Profiler for 90 percent of the monitoring that I do on my databases, there are times when I want to monitor for a longer period of time, or when I want to monitor only at certain points of time. For these instances, writing and scheduling my own custom traces to run has proved invaluable.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

————————————————————————————————————————————-

Get SQL tips in your inbox

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

Editor's Picks

Free Newsletters, In your Inbox