Have you ever tried to query the contents of the default trace and then spent more time trying to figure out the name of the oldest file than the actually analyzing the data?

A while back I wrote a query to quickly get the oldest file name in the default trace and then retrieve data from all the trace files to research.

I’ve found that this is handy because the number of trace files is configurable. On some instances the default configuration of 5 files may roll over within 5 min, leaving next to no time to troubleshoot.

I’ve placed the script on TechNet at https://gallery.technet.microsoft.com/Retrieving-data-from-the-ba14b07c

Let’s take a look at what’s included

This first bit gets the oldest file and displays the oldest recorded event time to give you and idea of how much data you’re working with:

Next we run through a few helpful queries:

Summarize all the events across all the traces

What you’re looking for here is a bad ratio of events. Generally, “Missing Join Predicate” and “Missing Column Statistics” should be a fairly low number.

“Server Memory Change”, Log File Auto Grow”, “Data File Auto Grow”, and “Error Log” should be even lower.

Let’s say that you see “Log File Auto Grow” events quite frequently. This could be an indication that your log file growth settings are not optimal and as a result increasing VLFs within the log. You’d want to take action!

Display Error Log data from the Default Trace

There are errors? Let’s find out what they are…

Display data for other concerning events

Finally, we query some of those concerning events we previously mentioned. This will help us do a bit of research into these issues by giving the Database Name, File Name, how long a growth took, and even the user name.

I hope you find this simple script as helpful as I have.

If you liked this blog, be sure to check out my others on SQL Server Central: http://www.sqlservercentral.com/blogs/confessions-of-a-microsoft-addict/