Maintaining SQL Server default trace historical events for analysis and reporting

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


I often see questions online where someone wants to find out who started a trace, when tempdb last had an autogrow event, or when the last full backup for master occurred. Usually we have to tell them that they *might* be able to get this information. Since these and other events are captured by the default trace, but the default trace only keeps five 20MB rollover files by default. This means that the event you are after may no longer be there, depending on how long ago it was and how busy your server happens to be. Unfortunately, people often need to find this information well after the fact, and in a lot of these cases the data is no longer available.


It occurred to me that it would be fairly trivial to set up a job that keeps as much of this history for you as you want. All you need is a table representing an initial "snapshot" of the default trace data, and a job that is responsible for (a) adding any new events to this table periodically (filtering out any "irrelevant" events), and (b) trimming out old history. You can use your own definitions here for "irrelevant" or "old" (more on this below).

To get started, in some permanent database other than tempdb, you can create an initial "snapshot" of the current default trace data with the following code. You might want to choose different columns here depending on what kind of troubleshooting you are hoping to get help with. I've left out performance-related columns such as Reads, Writes, CPU, PlanHandle, etc., since none of the events I am interested in for this example record that data anyway. Remember that the more data that you store in this history table, the less history you'll be able to store given the same disk space.

 CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
  FROM sys.traces WHERE is_default = 1;
 TextData = CONVERT(NVARCHAR(MAX), TextData),
 SqlHandle = CONVERT(VARBINARY(MAX), SqlHandle)
INTO dbo.DefaultTrace_History
FROM sys.fn_trace_gettable(@path, DEFAULT);
CREATE CLUSTERED INDEX IX_StartTime ON dbo.DefaultTrace_History(StartTime);

The clustered index on the StartTime column will be a great help both when trying to find data within a certain date range and also when the background job is trying to append or delete data. You might also consider non-clustered index on EventClass or any column where you think you might be performing targeted searches.

A query to add new data periodically would look like this, making sure to filter out any data from the default trace that you've already captured in your history table:

SELECT @maxDT = MAX(StartTime)
  FROM dbo.DefaultTrace_History;
INSERT dbo.DefaultTrace_History
 TextData = CONVERT(NVARCHAR(MAX), TextData),
 SqlHandle = CONVERT(VARBINARY(MAX), SqlHandle)
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE StartTime > @maxDT;

And a query to purge old data, say if you wanted to keep a max of 90 days in history, would look like this:

DELETE dbo.DefaultTrace_History 

You could schedule these in the same job and run them as frequently as you like - important, though, to balance the need to ensure you capture all information with the impact on the system. Every five minutes is way too often, and once a month is not often enough. I would say once a day is probably a safe interval.

You might want to keep the logic in two separate procedures, though. For example if you were doing some emergency troubleshooting, you might want to call the procedure that inserts new data before you start, so that all of the data in the history table is up to date (without also deleting any of the oldest data).

Now, getting back to the irrelevant data that you might want to filter out of the query. You can discover the events that will be captured by the default trace with the following query:

 EventID  = e.trace_event_id,
 Category =,
 [Event]  =
FROM sys.traces AS t
CROSS APPLY sys.fn_trace_geteventinfo( AS ti
INNER JOIN sys.trace_events AS e
ON ti.eventid = e.trace_event_id
INNER JOIN sys.trace_categories AS c
ON e.category_id = c.category_id
WHERE t.is_default = 1
GROUP BY e.trace_event_id,,
ORDER BY Category, [Event];

In SQL Server 2012, there are 34 events that are captured; the results are:

EventID Category                Event
---     ----------------------- ----------------------------------------------------
 92     Database                Data File Auto Grow
 94     Database                Data File Auto Shrink
167     Database                Database Mirroring State Change
 93     Database Log File       Auto Grow
 95     Database Log File       Auto Shrink
 22     Errors and Warnings     ErrorLog
 55     Errors and Warnings     Hash Warning
 79     Errors and Warnings     Missing Column Statistics
 80     Errors and Warnings     Missing Join Predicate
 69     Errors and Warnings     Sort Warnings
155     Full text               FT:Crawl Started
156     Full text               FT:Crawl Stopped
164     Objects                 Object:Altered
 46     Objects                 Object:Created
 47     Objects                 Object:Deleted
218     Performance             Plan Guide Unsuccessful
109     Security Audit          Audit Add DB User Event
108     Security Audit          Audit Add Login to Server Role Event
110     Security Audit          Audit Add Member to DB Role Event
111     Security Audit          Audit Add Role Event
104     Security Audit          Audit Addlogin Event
115     Security Audit          Audit Backup/Restore Event
117     Security Audit          Audit Change Audit Event
152     Security Audit          Audit Change Database Owner
102     Security Audit          Audit Database Scope GDR Event
116     Security Audit          Audit DBCC Event
106     Security Audit          Audit Login Change Property Event 
 20     Security Audit          Audit Login Failed
105     Security Audit          Audit Login GDR Event
103     Security Audit          Audit Schema Object GDR Event
153     Security Audit          Audit Schema Object Take Ownership Event
175     Security Audit          Audit Server Alter Trace Event
 18     Security Audit          Audit Server Starts And Stops
 81     Server                  Server Memory Change

(Please review this article which will give you a better idea of what events and columns you want to track and what data you don't consider important. The example above is just one way to configure your history table and may not be the right setup for you.)

If you had a few of these events you wanted to leave out (e.g. Audit Login Failed because you're monitoring those elsewhere, or any of the execution plan warnings) you could filter them out this way:

WHERE StartTime > @maxDT
AND EventClass NOT IN (20, 55, 69, 79, 80);

Similarly, if you wanted to have much more targeted monitoring for very specific events (e.g. only Auto Grow events), then you could change the query this way:

WHERE StartTime > @maxDT
AND EventClass IN (92, 93);

If you follow this guideline, you'll have much higher confidence to be able to track down specific events after the fact, without having to worry about whether they've rolled off the default trace already (and without having to hammer the default trace on your production system). You could insulate your production instance even further by replicating or mirroring this data, or sending the data to a linked server and never storing it locally in the first place.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog,, and also blogs at

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

Wednesday, August 8, 2012 - 3:12:07 AM - Sreeni Back To Top (18948)

How could i get select command start & end time(Means execution time of the query) using auditing in sqlserver-2008?

Tuesday, May 8, 2012 - 7:54:28 PM - RVantrease Back To Top (17348)

I doubt this would ever be a problem, but it is something to consider...

Since events can have the same start time, I think it would be a good idea to harvest events from current date minus one second.  That way, you know for sure that all the events in the default trace for the MAX(StartTime) are included for that start time when you do your next update.

This is what I came up with:


get free sql tips
agree to terms