Performance - Manage and Retrieve Information from the Default Trace

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Unkown
Date:        2 May 2012
                4 Feb 2013 - ChillyDBA - added an example of extending the queries to cover historical trace files too.

Description

This page contains several self-explanatory pieces of code to manage and retrieve SQL Server Default trace information:

  1. Display Default trace Configuration Information
  2. Enable Default Trace Logging 
  3. Display Default trace File Information
  4. View Current Default Trace File Data (ALL)
  5. View Current Default Trace File Data (CREATE/DELETE Events)
  6. View Current Default Trace File Data (AUTOGROW Events)
  7. View Current Default Trace File Data (SECURITY Events)

  8. View All Trace File Data Example (AUTOGROW Events) - retrieves data from previously written trace files in the sequence.  Provides an indication of the star and end date scope of the sequence.  Assumes that the default trace file naming is utilized and that only 5 files are retained.

Code

Display Default trace Configuration Information:

--Listing 1: Showing the configured settings for the default trace

SELECT   * FROM sys.configurations
WHERE   name LIKE 'default trace enabled'

Enable Default Trace Logging:

--Listing 2: Enable the default trace

USE   MASTER;
GO
EXEC   sp_configure 'show advanced option',   '1';
RECONFIGURE
GO
EXEC   sp_configure 'default trace enabled',   1
RECONFIGURE

Display Default trace File Information:

--Listing 3: Displaying information about the default trace file

SELECT   * FROM sys.traces
WHERE id = 1;

Display Default trace Configuration Information:

--Listing 4: View the current default trace file information

DECLARE   @filename NVARCHAR(1000);

-- Get the name of the current default trace
SELECT   @filename = CAST(value AS NVARCHAR(1000))
FROM   ::fn_trace_getinfo(DEFAULT)
WHERE   traceid = 1 AND   property = 2;

-- view current trace file
SELECT   *
FROM   ::fn_trace_gettable(@filename, DEFAULT) AS ftg
INNER   JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id  
  
ORDER BY   ftg.StartTime

View Current Default Trace File Data (CREATE/DELETE Events):

--Listing 5: Displaying object CREATE and DELETE events  

DECLARE   @filename NVARCHAR(1000);

-- Get the name of the current default trace
SELECT   @filename = CAST(value AS NVARCHAR(1000))
FROM   ::fn_trace_getinfo(DEFAULT)
WHERE   traceid = 1 AND   property = 2;

-- view current trace file
SELECT   *
FROM   ::fn_trace_gettable(@filename, DEFAULT) AS ftg
INNER   JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id  
WHERE (ftg.EventClass = 46 OR ftg.EventClass = 47)
AND  
DatabaseName <> 'tempdb'
AND   EventSubClass = 0
ORDER   BY ftg.StartTime;

View Current Default Trace File Data (AUTOGROW Events):

--Listing 6: Display Auto-Grow events

DECLARE   @filename NVARCHAR(1000);

-- Get the name of the current default trace
SELECT   @filename = CAST(value AS NVARCHAR(1000))
FROM   ::fn_trace_getinfo(DEFAULT)
WHERE   traceid = 1 AND   property = 2;

-- Find auto growth events in the current trace file
SELECT
    
ftg.StartTime
,te.name AS EventName
,DB_NAME(ftg.databaseid) AS DatabaseName  
,ftg.Filename
,(ftg.IntegerData*8)/1024.0 AS GrowthMB
,(ftg.duration/1000)AS DurMS
FROM   ::fn_trace_gettable(@filename, DEFAULT) AS ftg
INNER   JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id  
WHERE (ftg.EventClass = 92  -- Date File Auto-grow
      
OR ftg.EventClass   = 93) -- Log File Auto-grow
ORDER BY   ftg.StartTime

View Current Default Trace File Data (SECURITY Events):

--Listing 7: Showing Security Related Events

DECLARE   @filename NVARCHAR(1000);

-- Get the name of the current default trace
SELECT   @filename = CAST(value AS NVARCHAR(1000))
FROM   ::fn_trace_getinfo(DEFAULT)
WHERE   traceid = 1 AND   property = 2;

-- process all trace files
SELECT   *  
FROM   ::fn_trace_gettable(@filename, DEFAULT) AS ftg
INNER   JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id  
WHERE   ftg.EventClass
      
IN (102,103,104,105,106,108,109,110,111)
  
ORDER BY   ftg.StartTime


View All Trace File Data Example(AUTOGROW Events):

-- Listing 8 - an example of how to retrieve events from all trace files in the default trace sequence.
DECLARE  
  
@filename           NVARCHAR(1000),
  
@filename2          NVARCHAR(1000),
  
@filename3          NVARCHAR(1000),
  
@filename4          NVARCHAR(1000),
  
@filename5          NVARCHAR(1000),
  
@filename_part1     NVARCHAR(1000),
  
@filename_number    INT,
  
@trace_startdate    DATETIME,
  
@trace_enddate      DATETIME
  

-- Get the name of the current default trace active file
SELECT   @filename = CAST(value AS NVARCHAR(1000))
FROM   ::fn_trace_getinfo(DEFAULT)
WHERE   traceid = 1 AND   property = 2;

-- in a default configuration, there will be 5 traces files with incrementing numbers, with the highest being the last
-- it should therefore be a simple case of extracting the number, derementing it and creating the name of the previous files
SELECT
  
@filename_part1 =    REVERSE(SUBSTRING(REVERSE(@filename), CHARINDEX('_',REVERSE(@filename)), 1000)),
  
@filename_number =   CAST(REVERSE(SUBSTRING(REVERSE(@filename),5, CHARINDEX('_',REVERSE(@filename))- 5)) AS INT)

SELECT @filename2 = @filename_part1 + CAST((@filename_number - 1) AS NVARCHAR(1000)) + '.trc'
SELECT @filename3 = @filename_part1 + CAST((@filename_number - 2) AS NVARCHAR(1000)) + '.trc'
SELECT @filename4 = @filename_part1 + CAST((@filename_number - 3) AS NVARCHAR(1000)) + '.trc'
SELECT @filename5 = @filename_part1 + CAST((@filename_number - 4) AS NVARCHAR(1000)) + '.trc'

-- just to give an idea of scope of the trace, get the start and end dates and the #days encompassed by the trace file series
SELECT @trace_startdate =     MIN(ftg.StartTime)
FROM   ::fn_trace_gettable(@filename5, DEFAULT) AS ftg

SELECT @trace_enddate =     MAX(ftg.StartTime)
FROM   ::fn_trace_gettable(@filename, DEFAULT) AS ftg

SELECT
    
@trace_startdate                                       AS TraceStartDate,
    
@trace_enddate                                         AS TraceEndDate,
    
DATEDIFF(dd,  @trace_startdate, @trace_enddate)           AS TraceDurationDays


-- Find auto growth events in the all trace files
SELECT
    
ftg.StartTime
,te.name AS EventName
,DB_NAME(ftg.databaseid) AS DatabaseName  
,ftg.Filename
,(ftg.IntegerData*8)/1024.0 AS GrowthMB
,(ftg.duration/1000)AS DurMS
FROM   ::fn_trace_gettable(@filename, DEFAULT) AS ftg
INNER   JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id  
WHERE (ftg.EventClass = 92  -- Date File Auto-grow
    
OR ftg.EventClass   = 93) -- Log File Auto-grow

UNION

-- Find auto growth events in the current trace file
SELECT
    
ftg.StartTime
,te.name AS EventName
,DB_NAME(ftg.databaseid) AS DatabaseName  
,ftg.Filename
,(ftg.IntegerData*8)/1024.0 AS GrowthMB
,(ftg.duration/1000)AS DurMS
FROM   ::fn_trace_gettable(@filename2, DEFAULT) AS ftg
INNER   JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id  
WHERE (ftg.EventClass = 92  -- Date File Auto-grow
    
OR ftg.EventClass   = 93) -- Log File Auto-grow

UNION

SELECT
    
ftg.StartTime
,te.name AS EventName
,DB_NAME(ftg.databaseid) AS DatabaseName  
,ftg.Filename
,(ftg.IntegerData*8)/1024.0 AS GrowthMB
,(ftg.duration/1000)AS DurMS
FROM   ::fn_trace_gettable(@filename3, DEFAULT) AS ftg
INNER   JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id  
WHERE (ftg.EventClass = 92  -- Date File Auto-grow
    
OR ftg.EventClass   = 93) -- Log File Auto-grow

UNION

SELECT
    
ftg.StartTime
,te.name AS EventName
,DB_NAME(ftg.databaseid) AS DatabaseName  
,ftg.Filename
,(ftg.IntegerData*8)/1024.0 AS GrowthMB
,(ftg.duration/1000)AS DurMS
FROM   ::fn_trace_gettable(@filename4, DEFAULT) AS ftg
INNER   JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id  
WHERE (ftg.EventClass = 92  -- Date File Auto-grow
    
OR ftg.EventClass   = 93) -- Log File Auto-grow

UNION

SELECT
    
ftg.StartTime
,te.name AS EventName
,DB_NAME(ftg.databaseid) AS DatabaseName  
,ftg.Filename
,(ftg.IntegerData*8)/1024.0 AS GrowthMB
,(ftg.duration/1000)AS DurMS
FROM   ::fn_trace_gettable(@filename5, DEFAULT) AS ftg
INNER   JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id  
WHERE (ftg.EventClass = 92  -- Date File Auto-grow
    
OR ftg.EventClass   = 93) -- Log File Auto-grow



ċ
Get Default Trace information (all files example).sql
(4k)
Andy Hughes,
Feb 4, 2013, 9:22 AM
ċ
Get Default Trace information.sql
(3k)
Andy Hughes,
Aug 23, 2012, 12:15 PM
Comments