Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast













































   Got a SQL tip?
            We want to know!

Maintaining SQL Server default trace historical events for analysis and reporting

MSSQLTips author Aaron Bertrand By:   |   Read Comments (2)   |   Related Tips: More > 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


Last Update: 5/8/2012


About the author
MSSQLTips author Aaron Bertrand
Aaron Bertrand is a Senior Consultant at SQL Sentry, Inc., and has been contributing to the community for over a decade, first earning the Microsoft MVP award in 1997.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Wednesday, August 08, 2012 - 3:12:07 AM - Sreeni Read The Tip

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


Tuesday, May 08, 2012 - 7:54:28 PM - RVantrease Read The Tip

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:

WHERE StartTime < DATEADD(S, -1 GETDATE())




 
Sponsor Information







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