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