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.
DECLARE @path NVARCHAR(260);
SELECT @path = REVERSE(SUBSTRING(REVERSE([path]),
CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
FROM sys.traces WHERE is_default = 1;
TextData = CONVERT(NVARCHAR(MAX), TextData),
SqlHandle = CONVERT(VARBINARY(MAX), SqlHandle)
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:
And a query to purge old data, say if you wanted to keep a max of 90 days in history, would look like this:
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:
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.
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.
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.