By: Aaron Bertrand | Comments (2) | Related: > Profiler and Trace
Problem
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.
Solution
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.
DECLARE @path NVARCHAR(260);
SELECT @path = REVERSE(SUBSTRING(REVERSE([path]),
CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
FROM sys.traces WHERE is_default = 1;
SELECT
TextData = CONVERT(NVARCHAR(MAX), TextData),
DatabaseID,
HostName,
ApplicationName,
LoginName,
SPID,
StartTime,
EndTime,
Duration,
ObjectID,
ObjectType,
IndexID,
EventClass,
[FileName],
RowCounts,
IsSystem,
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:
DECLARE @maxDT DATETIME; SELECT @maxDT = MAX(StartTime) FROM dbo.DefaultTrace_History; INSERT dbo.DefaultTrace_History SELECT TextData = CONVERT(NVARCHAR(MAX), TextData), DatabaseID, HostName, ApplicationName, LoginName, SPID, StartTime, EndTime, Duration, ObjectID, ObjectType, IndexID, EventClass, [FileName], RowCounts, IsSystem, 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 WHERE StartTime < DATEADD(DAY, -90, CURRENT_TIMESTAMP);
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:
SELECT EventID = e.trace_event_id, Category = c.name, [Event] = e.name FROM sys.traces AS t CROSS APPLY sys.fn_trace_geteventinfo(t.id) 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, c.name, e.name 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
- Create an initial snapshot of your current default trace to record history over time.
- Set up a job to append new data to the history table, and purge old data.
- Review the following tips and other resources:
About the author
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, SQLPerformance.com, and also blogs at sqlblog.org.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
