Using the Default Trace in SQL Server

By:   |   Comments (3)   |   Related: > Profiler and Trace


I have heard of the default trace in SQL Server. I know it's on by default, but I don't know how to use it. What can I do with the default trace?


The default trace is a new feature first included with SQL Server 2005 which provides auditing on schema modifications such as table creation and stored procedure deletion and the like. It does run by default, but you can turn it on and off using sp_configure as detailed in a previous tip.

If you are using SQL Server 2008 or later, you can enforce whether it is on or off using Policy Management. The server configuration facet has a parameter called @DefaultTraceEnabled which corresponds to turning on or off the default trace. For instance, here is a Policy Management Condition which enforces the default trace being on:

Policy Management Condition - Enforcing Default Trace

Using the Schema Changes History Report

If you have the default trace running, you are able to easily bring up DDL changes both at a database or a server level by looking at the Schema Changes History report. You can get to this report at the database level by right-clicking on the database, choosing Reports, then choosing Standard Reports, and finally selecting Schema Changes History.

Selecting Schema Changes History report

If you want to see all the changes at the server level, you would simply right-click on the server and begin the navigation. An example of the report is shown below. Note that who made the change and when it was made is captured.

Schema Changes History report example

Using SQL Profiler

Another way of using the default trace is via SQL Server Profiler. The files for the default trace are stored in the default LOG directory for your particular SQL Server. You'll be looking for the smallest numbered log file:

Selecting the first trace file

You'll be prompted for file rollover for each additional trace file. The prompt should look something like this:

Trace file rollover

You'll want to select Yes for each time you are prompted. This will allow Profiler to show the complete contents stored in the default trace file. Once you've done so, you can examine the trace, just as you would any other. There's a lot of information stored in the trace, so I've taken a snapshot and highlighted the CREATE TABLE we saw in the Schema Changes History report:

Profiler - Default Trace

Keep in mind that when viewing the default trace information in this way, that the default trace file will contain information for the entire server. Therefore, keying in on the database name is important. Also, at a casual glance of the screenshot, you can see that more than just the table reported by the Schema Changes History report is captured in the trace file. For instance, the database autogrowth of tempdb can be seen as well as creation of some statistics within the FortressSQLServer database can be seen.

Using T-SQL

The default trace should be the first numbered trace running on your SQL Server. However, you can verify by looking at the value corresponding to the trace file location. This simple query will return that information for all traces running on the system. You're looking for a location that matches your default LOG folder.

SELECT traceidvalue FROM [fn_trace_getinfo](NULL)
WHERE [property] 2;

Once you have the traceID, you can then query the default trace file using the fn_trace_gettable() function. One catch if you use the fn_trace_getinfo() function to determine that the default trace is running: it will return the most current trace file that is open. When I ran it, I got a value that ended in log_131.trc. However, the starting trace file was log_127.trc. I had to look in the directory to determine the correct file to start from. Once you have that, you can display the results as a resultset using the following query (substitute the starting trace file accordingly):

SELECT FROM [fn_trace_gettable]('C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\LOG\log_127.trc'DEFAULT)
ORDER BY StartTime;

I've ordered it by StartTime, just to get a reasonable order. However, you could also use a SELECT INTO and an IDENTITY() column if you need the order perfectly maintained.

Next Steps
  • I have shown you how you can pull information that SQL Server is already gathering using the default trace.  Next time you need to find some information see if the default trace has already captured the data before starting a new trace

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, April 28, 2009 - 12:25:28 PM - admin Back To Top (3275)

The code in the tip has been corrected to reflect this:

SELECT traceidvalue 
FROM [fn_trace_getinfo]
WHERE [property] 2;

Tuesday, April 28, 2009 - 6:33:43 AM - unclebiguns Back To Top (3270)


Nice post.  When I query the default trace using sys.traces like this:

    sys.traces AS T Cross Apply
                            WHEN CHARINDEX( '_',T.[path]) <> 0 THEN
                            SUBSTRING(T.PATH, 1, CHARINDEX( '_',T.[path])-1) + '.trc'
                            ELSE T.[path]
                        End, T.max_files) AS FTG
    T.is_default = 1

Tuesday, April 28, 2009 - 6:24:11 AM - K. Brian Kelley Back To Top (3269)

The first bit of code is this:

SELECT traceidvalue 
FROM [fn_trace_getinfo]
WHERE [property] 2;


get free sql tips
agree to terms