Learn more about SQL Server tools

solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips

































Top SQL Server Tools






















Using the Default Trace in SQL Server 2005 and SQL Server 2008

MSSQLTips author K. Brian Kelley By:   |   Read Comments (3)   |   Related Tips: More > Profiler and Trace

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

Solution
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, 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


Last Update: 4/28/2009


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

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Tuesday, April 28, 2009 - 6:24:11 AM - K. Brian Kelley Read The Tip

The first bit of code is this:

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

 


Tuesday, April 28, 2009 - 6:33:43 AM - unclebiguns Read The Tip

Brian,

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

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

Tuesday, April 28, 2009 - 12:25:28 PM - admin Read The Tip

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

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



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.