Sign up to our newsletter...

...and be the first to learn about new releases

What Is The Default Trace?
And how do I look at it?

By Richard Fryar

The default trace is a server side trace that starts automatically when the SQL Server service is started.

It traces some of the activities and events that you may be interested in when investigating recent activity on your server.

The default trace is present in all versions of SQL Server since 2005, but is deprecated in 2012, so although it is still present there is no guarantee it will exist in future versions (extended events are taking over).

The default trace can be disabled by setting the 'default trace enabled' option to 0 with sp_configure. You may want to do this if you feel the performance overhead of the default trace is affecting overall performance but this is very unlikely unless the server is already struggling (it is very lightweight) and it's usefulness normally outweighs any performance impact.

What Events Are In The Default Trace?

The events in the default trace include the following:

CategoryEvents
DatabaseData File Auto Grow
Data File Auto Shrink
Database Mirroring State Change
Log File Auto Grow
Log File Auto Shrink
Errors and Warnings ErrorLogHash Warnings
Missing Column Statistics
Missing Join Predicate
Sort Warnings
Full textFT:Crawl Aborted (not 2012)
FT:Crawl Started
FT:Crawl Stopped
ObjectsObject:Altered
Object:Created
Object:Deleted
PerformancePlan Guide Unsuccessful (2008 SP1 onwards)
Security AuditAudit Add DB User Event
Audit Add Login to Server Role Event
Audit Add Member to DB Role Event
Audit Add Role Event
Audit Addlogin Event
Audit Backup/Restore Event
Audit Change Database Owner
Audit DBCc Event
Audit Database Scope GDR Event
Audit Login Change Property Event
Audit Login Failed
Audit Login GDR Event
Audit Schema Object GDR Event
Audit Schema Object Take Ownership Event
Audit Server Starts and Stops
ServerServer Memory Change

How Do I View The Default Trace?

The default trace consists of a maximum of 5 trace files. Each file is closed when it reaches 20MB in size, and events roll over into a new one. The oldest file is deleted so there are never more than 5. On a busy server, this may mean you can only go back a few minutes.

If you want to keep more than this, you could create a job to copy the older files into a different location - preferably on a different server.

To answer part of the original question, there is no way of viewing the default trace in Management Studio.

You have 3 options:

Profiler

SQL Server Profiler can display the default trace in the same way as any other trace. Double click on one of the files to open it. If it is not the most recent file you will be ask if you want the next file opened.

Script

Another way to view the default trace is via TSQL.

The function fn_trace_getable opens one or more trace files and returns the result as a table. This can be joined onto sys.trace_events to get the descriptive names for each event.

You can get the name of the current trace file by querying sys.traces as follows:

SELECT path FROM sys.traces WHERE is_default = 1

And this TSQL query filters on category 2 (Database) to see what file grow/shrink and mirroring state change events have occurred.

SELECT df.StartTime,
  
e.name,
  
df.DatabaseName,
  
df.ApplicationName,
  
df.LoginName,
  
df.SPID,
  
df.Duration,
  
df.EndTime,
  
df.FileName,
  
df.TEXTData,
  
df.IntegerData
FROM ::fn_trace_gettable('F:\MSSQL10_50.A\MSSQL\Log\log_113.trc', 0) df
JOIN sys.trace_events e ON df.EventClass = e.trace_event_id
WHERE e.category_id = 2
ORDER BY df.StartTime DESC

Third Party

And finally, there are third party tools available that make the task much easier.

Here is a screenshot from SQL CoPilot showing that I have just shrunk the AdventureWorks database.

Default Trace Example