SQL Server 2005 Performance Dashboard Reports - Professional SQL Server 2005 Performance Tuning - page 476

RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'
RECONFIGURE WITH OVERRIDE
GO

The contents of the default trace can be displayed like any other trace with the fn_trace_gettable
function. It stores the data in up to five trace files with a 20 MB rollover threshold. If you want to see all
of the events that have been persisted into the trace files, perform the following steps:

1.

Query the sys.traces catalog view to get the file name of the default trace.

2.

Parse out the folder path and append log.trc to it.

3.

Pass in the name of the base log file to fn_trace_gettable .

Here is a sample script:

--Variables
DECLARE @default_trace_current_filename NVARCHAR(520);
DECLARE @default_trace_filename NVARCHAR(520);
DECLARE @index int;
DECLARE @max_files int;

--Get the current filename for the default trace
SELECT @default_trace_current_filename = [path]
FROM sys.traces
WHERE is_default = 1;

--Reverse the string
SET @default_trace_current_filename =
REVERSE(@default_trace_current_filename)

--Find the last ' \ '
SELECT @index = PATINDEX('% \ %', @default_trace_current_filename)

--Revert the string back to normal
SET @default_trace_current_filename =
REVERSE(@default_trace_current_filename)

--Parse out the directory name and append 'log.trc'
SET @default_trace_filename = LEFT(@default_trace_current_filename ,
LEN(@default_trace_current_filename) - @index) + ' \ log.trc';

--Display metadata for the default trace
SELECT *
FROM sys.traces
WHERE is_default = 1;