Manage and Retrieve Information from the Default Trace

--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

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

SELECT * FROM sys.traces

WHERE id = 1;

--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

--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;

--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

--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

-- 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