Wednesday, January 23, 2013

Query All Default Trace Files at Once

Happy Wednesday!

I wanted to share something I came across today when I was researching a way to query ALL the default traces produced by SQL Server.  I knew how to query for the path and name of the current trace, but since default traces only grow to 20MB before rolling over to a fresh one, often times the information I want to know isn't in the current trace.  What I really want is to query data from all five at one time.

To query all five default trace files I figured I was going to have to write some fancy parser that would loop through and decrement the numeric suffix in the trace file naming convention.  For example, if the current default trace is named log_409.trc, the previous four would be log_408.trc, log_407.trc, etc.  Fortunately what I'm going to share is that the names of these files doesn't matter.  If you query for the current default trace and replace the file name with just log.trc (an invalid trace file name), you will in fact be querying all five at once.  What a golden nugget!

In my example I'm going to share the script I use to query for all auto-growth and auto-shrink activities.  Keep in mind this isn't an open debate about auto-shrink - it's just an example ;-)  If you run this script using a Central Management Server, you can query a whole host of instances at the same time even if the path to their default traces differ.

DECLARE @filename NVARCHAR(4000);

-- Current default trace
SELECT  @filename = CAST(value AS NVARCHAR(4000))
FROM    ::
WHERE   traceid = 1
        AND property = 2

-- Preserve the path and replace the current default trace with log.trc
SET @filename = LEFT(@filename,
                     LEN(@filename) - CHARINDEX('\',REVERSE(@filename)))
    + '\log.trc'

-- Auto shrink/growth events in the current trace file
SELECT  DB_NAME(ftg.databaseid) AS DatabaseName
       , AS EventName
       ,MIN(ftg.StartTime) AS FirstOccur
       ,MAX(ftg.EndTime) AS LastFinished
       ,COUNT(*) AS NumOccur
       ,( SUM(ftg.IntegerData * 8) ) / 1024.0 AS AmountMB
       ,( SUM(ftg.duration / 1000) ) AS MilliSec
        INNER   JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id
WHERE   te.category_id = 2
GROUP BY DB_NAME(ftg.databaseid)
ORDER BY MIN(ftg.StartTime) DESC