SQL Server

SQL Server 2008 : Defining Server-Side Traces

3/19/2011 9:10:22 AM
Much of the SQL Server Profiler functionality can also be initiated through a set of system stored procedures. Through these procedures, you can define a server-side trace that can be run automatically or on a scheduled basis, such as via a scheduled job, instead of through the Profiler GUI. Server-side traces are also useful if you are tracing information over an extended period of time or are planning on capturing a large amount of trace information. The overhead of running a server-side trace is less than that of running a client-side trace with Profiler.

To start a server-side trace, you need to define the trace by using the trace-related system procedures. These procedures can be called from within a SQL Server stored procedure or batch. You define a server-side trace by using the following four procedures:

  • sp_trace_create— This procedure is used to create the trace definition. It sets up the trace and defines the file to store the captured events. sp trace create returns a trace ID number that you need to reference from the other three procedures to further define and manage the trace.

  • sp_trace_setevent— You need to call this procedure once for each data column of every event that you want to capture.

  • sp_trace_setfilter— You call this procedure once for each filter you want to define on an event data column.

  • sp_trace_setstatus— After the trace is defined, you call this procedure to start, stop, or remove the trace. You must stop and remove a trace definition before you can open and view the trace file.

You will find that manually creating procedure scripts for tracing can be rather tedious. Much of the tedium is due to the fact that many numeric parameters drive the trace execution. For example, the sp_trace_setevent procedure accepts an eventid and a columnid that determine what event data will be captured. Fortunately, SQL Server 2008 provides a set of catalog views that contain these numeric values and what they represent. The sys.trace_categories catalog view contains the event categories. The sys.trace_events catalog view contains the trace events, and sys.trace_columns contains the trace columns. The following SELECT statement utilizes two of these system views to return the available events and their related categories:

select e.trace_event_id, 'Event Name', 'Category Name'
from sys.trace_events e
join sys.trace_categories c on e.category_id = c.category_id
order by e.trace_event_id

The results of this SELECT statement are shown in Table 1.

Table 1. Trace Events and Their Related Categories
trace_event_idEvent NameCategory Name
10RPC:CompletedStored Procedures
11RPC:StartingStored Procedures
14Audit LoginSecurity Audit
15Audit LogoutSecurity Audit
16AttentionErrors and Warnings
18Audit Server Starts And StopsSecurity Audit
20Audit Login FailedSecurity Audit
21EventLogErrors and Warnings
22ErrorLogErrors and Warnings
28Degree of ParallelismPerformance
33ExceptionErrors and Warnings
34SP:CacheMissStored Procedures
35SP:CacheInsertStored Procedures
36SP:CacheRemoveStored Procedures
37SP:RecompileStored Procedures
38SP:CacheHitStored Procedures
42SP:StartingStored Procedures
43SP:CompletedStored Procedures
44SP:StmtStartingStored Procedures
45SP:StmtCompletedStored Procedures
55Hash WarningErrors and Warnings
58Auto StatsPerformance
59Lock:Deadlock ChainLocks
67Execution WarningsErrors and Warnings
68Showplan Text (Unencoded)Performance
69Sort WarningsErrors and Warnings
71Prepare SQLT-SQL
72Exec Prepared SQLT-SQL
73Unprepare SQLT-SQL
79Missing Column StatisticsErrors and Warnings
80Missing Join PredicateErrors and Warnings
81Server Memory ChangeServer
82UserConfigurable:0User configurable
83UserConfigurable:1User configurable
84UserConfigurable:2User configurable
85UserConfigurable:3User configurable
86UserConfigurable:4User configurable
87UserConfigurable:5User configurable
88UserConfigurable:6User configurable
89UserConfigurable:7User configurable
90UserConfigurable:8User configurable
91UserConfigurable:9User configurable
92Data File Auto GrowDatabase
93Log File Auto GrowDatabase
94Data File Auto ShrinkDatabase
95Log File Auto ShrinkDatabase
96Showplan TextPerformance
97Showplan AllPerformance
98Showplan Statistics ProfilePerformance
100RPC Output ParameterStored Procedures
102Audit Database Scope GDR EventSecurity Audit
103Audit Schema Object GDR EventSecurity Audit
104Audit Addlogin EventSecurity Audit
105Audit Login GDR EventSecurity Audit
106Audit Login Change Property EventSecurity Audit
107Audit Login Change Password EventSecurity Audit
108Audit Add Login to Server Role EventSecurity Audit
109Audit Add DB User EventSecurity Audit
110Audit Add Member to DB Role EventSecurity Audit
111Audit Add Role EventSecurity Audit
112Audit App Role Change Password EventSecurity Audit
113Audit Statement Permission EventSecurity Audit
114Audit Schema Object Access EventSecurity Audit
115Audit Backup/Restore EventSecurity Audit
116Audit DBCC EventSecurity Audit
117Audit Change Audit EventSecurity Audit
118Audit Object Derived Permission EventSecurity Audit
119OLEDB Call EventOLEDB
120OLEDB QueryInterface EventOLEDB
121OLEDB DataRead EventOLEDB
122Showplan XMLPerformance
125Deprecation AnnouncementDeprecation
126Deprecation Final SupportDeprecation
127Exchange Spill EventErrors and Warnings
128Audit Database Management EventSecurity Audit
129Audit Database Object Management EventSecurity Audit
130Audit Database Principal Management EventSecurity Audit
131Audit Schema Object Management EventSecurity Audit
132Audit Server Principal Impersonation EventSecurity Audit
133Audit Database Principal Impersonation EventSecurity Audit
134Audit Server Object Take Ownership EventSecurity Audit
135Audit Database Object Take Ownership EventSecurity Audit
136Broker:Conversation GroupBroker
137Blocked process reportErrors and Warnings
139Broker:Forwarded Message SentBroker
140Broker:Forwarded Message DroppedBroker
141Broker:Message ClassifyBroker
143Broker:Queue DisabledBroker
144Broker:Mirrored Route State ChangedBroker
146Showplan XML Statistics ProfilePerformance
148Deadlock graphLocks
149Broker:Remote Message AcknowledgementBroker
150Trace File CloseServer
151Database Mirroring ConnectionDatabase
152Audit Change Database OwnerSecurity Audit
153Audit Schema Object Take Ownership EventSecurity Audit
154Audit Database Mirroring LoginSecurity Audit
155FT:Crawl StartedFull text
156FT:Crawl StoppedFull text
157FT:Crawl AbortedFull text
158Audit Broker ConversationSecurity Audit
186TM: Commit Tran completedTransactions
187TM: Rollback Tran startingTransactions
188TM: Rollback Tran completedTransactions
189Lock:Timeout (timeout > 0)Locks
190Progress Report: Online Index OperationProgress Report
191TM: Save Tran startingTransactions
192TM: Save Tran completedTransactions
193Background Job ErrorErrors and Warnings
194OLEDB Provider InformationOLEDB
195Mount TapeServer
196Assembly LoadCLR
198XQuery Static TypeT-SQL
199QN: SubscriptionQuery Notifications
200QN: Parameter tableQuery Notifications
201QN: TemplateQuery Notifications
202QN: DynamicsQuery Notifications
212Bitmap WarningErrors and Warnings
213Database Suspect Data PageErrors and Warnings
214CPU threshold exceededErrors and Warnings
217Plan Guide SuccessfulPerformance
218Plan Guide UnsuccessfulPerformance
235Audit FulltextSecurity Audit


The numeric IDs for the trace columns can be obtained from the sys.trace_columns catalog view, as shown in the following example:

select trace_column_id, name 'Column Name', type_name 'Data Type'
from sys.trace_columns
order by trace_column_id

Table 2 shows the results of this SELECT statement and lists all the available trace columns.

Table 2. Trace Columns Available for a Server-Side Trace
trace_column_idColumn NameData Type


You have to call the sp_trace_setevent procedure once for each data column you want captured for each event in the trace. Based on the number of events and number of columns, you can see that this can result in a lot of executions of the sp_trace_setevent procedure for a larger trace definition.

To set up filters, you must pass the column ID, the filter value, and numeric values for the logical operator and column operator to the sp_trace_setfilter procedure. The logical operator can be either 0 or 1. A value of 0 indicates that the specified filter on the column should be ANDed with any other filters on the column, whereas a value of 1 indicates that the OR operator should be applied. Table 3 describes the values allowed for the column operators.

Table 3. Column Operator Values for sp_trace_setfilter
ValueComparison Operator
0= (equal)
1<> (not equal)
2> (greater than)
3< (less than)
4>= (greater than or equal)
5<= (less than or equal)

Fortunately, there is an easier way of generating a trace definition script. You can set up your traces by using the SQL Profiler GUI and script the trace definition to a file. After you define the trace and specify the events, data columns, and filters you want to use, you select File, Export, Script Trace Definition. The SQL commands (including calls to the aforementioned system stored procedures) to define the trace, start the trace, and write the trace to a file are generated into one script file. You have the option to generate a script that works with SQL Server 2000, 2005 or 2008. Listing 1 shows an example of a trace definition exported from the Profiler. It contains the trace definitions for the TSQL trace template. You must replace the text InsertFileNameHere with an appropriate filename, prefixed with its pathname, before running this script.

Listing 1. A SQL Script for Creating and Starting a Server-Side Trace
/* Created by: SQL Server 2008 Profiler */
/* Date: 05/10/2009 07:20:54 PM */

-- 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'InsertFileNameHere', @maxfilesize,
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, 2, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 12, 1, @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

— 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




If you want to always capture certain trace events when SQL Server is running, such as auditing events, you can create a stored procedure that uses the sp_trace stored procedures to create a trace and specify the events to be captured. You can use the code in Listing 1 as a basis to create the stored procedure. Then you can mark the procedure as a startup procedure by using the sp_procoption procedure to set the autostart option. The trace automatically starts when SQL Server is started, and it continues running in the background.

Just be aware that although using server-side traces is less intrusive than using the SQL Profiler client, some overhead is necessary to run a trace. You should try to limit the number of events and number of columns captured to minimize the overhead as much as possible.

Monitoring Running Traces

SQL Server 2008 provides some additional built-in user-defined functions to get information about currently running traces. Like the fn_trace_gettable function discussed previously, these functions return the information as a tabular result. The available functions are as follows:

  • fn_trace_getinfo(trace_id) This function is passed a traceid, and it returns information about the specified trace. If passed the value of default, it returns information about all existing traces. An example of the output from this function is shown in Listing 2.

  • fn_trace_geteventinfo(trace_id) This function returns a list of the events and data columns being captured for the specified trace. Only the event and column ID values are returned. You can use the information provided in Tables 1 and 2 to map the IDs to the more meaningful event names and column names.

  • fn_trace_getfilterinfo(trace_id) This function returns information about the filters being applied to the specified trace. Again, the column ID and logical and comparison operator values are returned as integer IDs that you need to decipher. See Table 3 for a listing of the column operator values.

Listing 2. An Example of Using the Built-in User-Defined Functions for Monitoring Traces
SELECT * FROM ::fn_trace_getinfo(default)

traceid property value
1 1 2
1 2 C:\Program Files\Microsoft SQL Server\MSSQL.1\
1 3 20
1 4 NULL
1 5 1
2 1 0
2 2 c:\trace\mytrace.trc.trc
2 3 5
2 4 NULL
2 5 1

select * from ::fn_Trace_getfilterinfo(2)

columnid logical_operator comparison_operator value
3 0 0 6
10 0 7 Profiler
10 0 7 SQLAgent


You may be wondering why there is always a traceid with a value of 1 running when you run the fn_trace_getinfo procedure. This is the default trace that SQL Server automatically initiates when it starts. The default trace is enabled by default. You can identify which trace is the default by selecting from the sys.traces catalog view and examining the is_default column. The default trace captures a number of different types of events, including object creates and drops, errors, memory and disk changes, security changes, and more. You can disable this default trace, but it is generally lightweight and should be left enabled.

The output from the functions that return trace information is relatively cryptic because many of the values returned are numeric. For example, the property values returned by fn_trace_getinfo are specified as integer IDs. Table 6.5 describes of each of these property IDs.

Table 4. Description of Trace Property ID Values
Property IDDescription
1Trace options specified in sp_trace_create
2Trace filename
3Maximum size of trace file, in MB
4Date and time the trace will be stopped
5Current trace status

Stopping Server-Side Traces

It is important to keep track of the traces you have running and to ensure that “heavy” traces are stopped. Heavy traces are typically traces that capture a lot of events and are run on a busy SQL Server. These traces can affect the overall performance of your SQL Server machine and write a large amount of information to the trace output file. If you specified a stop time when you started the trace, it automatically stops and closes when the stop time is reached. For example, in the SQL script in Listing 6.2, if you wanted the trace to run for 15 minutes instead of indefinitely, you would set the value for the stoptime variable at the beginning of the script, using a command similar to the following:

   set @stoptime = dateadd(minute, 15, getdate())

To otherwise stop a running server-side trace, you use the sp_trace_setstatus stored procedure and pass it the trace ID and a status of 0. Stopping a trace only stops gathering trace information and does not delete the trace definition from SQL Server. Essentially, it pauses the trace. You can restart the trace by passing sp_trace_setstatus a status value of 1.

After you stop a trace, you can close the trace and delete its definition from SQL Server by passing sp_trace_setstatus the ID of the trace you want to stop and a status value of 2. After you close the trace, you must redefine it before you can restart it.

If you don’t know the ID of the trace you want to stop, you can use the fn_trace_getinfo function or the sys.traces catalog view to return a list of all running traces and select the appropriate trace ID. The following example shows how to stop and close a trace with a trace ID of 2:

-- Set the trace status to stop
exec sp_trace_setstatus 2, 0

-- Close and Delete the trace
exec sp_trace_setstatus 2, 2

If you want to stop and close multiple traces, you must call sp_trace_setstatus twice for each trace. Listing 3 provides an example of a system stored procedure that you can create in SQL Server to stop a specific trace or automatically stop all currently running traces.

Listing 3. A Sample System Stored Procedure to Stop Profiler Traces
use master
if object_id ('sp_stop_profiler_trace') is not null
drop proc sp_stop_profiler_trace

create proc sp_stop_profiler_trace @TraceID int = null

if @TraceID is not null
-- Set the trace status to stop
exec sp_trace_setstatus @TraceID, 0

-- Delete the trace
exec sp_trace_setstatus @TraceID, 2
-- the following cursor does not include the default trace
declare c1 cursor for
SELECT distinct traceid FROM :: fn_trace_getinfo (DEFAULT)
WHERE traceId not in (select ID from sys.traces where is_default = 1)
open c1
fetch c1 into @TraceID
while @@fetch_status = 0
-- Set the trace status to stop
exec sp_trace_setstatus @TraceID, 0

-- Delete the trace
exec sp_trace_setstatus @TraceID, 2
fetch c1 into @TraceID
close c1
deallocate c1

Other -----------------
- SQL Server 2008 : SQL Server Profiler - Replaying Trace Data
- SQL Server 2008 : SQL Server Profiler - Saving and Exporting Traces
- SQL Server 2008 : SQL Server Profiler - Creating Traces
- SQL Server 2008 : SQL Server Profiler Architecture
- SQL Server 2008: Administering Database Objects - Working with Tables (part 7) - Partitions
- SQL Server 2008: Administering Database Objects - Working with Tables (part 6) - Compression
- SQL Server 2008: Administering Database Objects - Working with Tables (part 5) - Sparse Columns
- SQL Server 2008: Administering Database Objects - Working with Tables (part 4) - Check Constraints
- SQL Server 2008: Administering Database Objects - Working with Tables (part 3) - Foreign Key Constraints
- SQL Server 2008: Administering Database Objects - Working with Tables (part 2) - Primary Key Constraints & Unique Constraints
- SQL Server 2008: Administering Database Objects - Working with Tables (part 1) - Default Constraints
- SQL Server 2008: Administering Database Objects - Working with Database Snapshots
- Programming with SQL Azure : WCF Data Services (part 3)
- Programming with SQL Azure : WCF Data Services (part 2) - Creating the Client Application
- Using XML in SQL Server 2008: Relational Data As XML - The FOR XML Modes (part 4) - EXPLICIT Mode
- Using XML in SQL Server 2008: Relational Data As XML - The FOR XML Modes (part 3) - AUTO Mode
- Programming with SQL Azure : WCF Data Services (part 1)
- Using XML in SQL Server 2008: Relational Data As XML - The FOR XML Modes (part 2) - Working with Binary Columns
- Using XML in SQL Server 2008: Relational Data As XML - The FOR XML Modes (part 1) - RAW Mode
- Programming with SQL Azure : Connecting to SQL Azure (part 4) - Sqlcmd
Most View
- SharePoint 2010 : Use the Ribbon
- Windows Phone 7 : Customizing Your E-Mail Signature
- iPhone SDK : Creating Basic GameKit Services (part 2) : Sending and Receiving Data
- jQuery 1.3 : Improving a basic form (part 4)
- Windows Small Business Server 2011 : Deploying Network Printers (part 1) - Creating a Printer - Installing a Local Printer Manually
- iPhone Programming : Other View Controllers - Utility Applications
- Developing for Windows Phone and Xbox Live : Sprites and 2D Graphics - Spritebatch (part 3)
- Using Non-Windows Systems to Access Exchange Server 2007 : Terminal Server Client for Mac
- An OLAP Requirements Example: CompSales International (part 6) - Creating the Cube
- Windows Phone 7 : Working with the Calendar - Sending an Invitation
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS