Hello Everyone,

Just looking over the article again and thought I would make a stored procedure which automatically gets the path and log name used by the default trace. This way you can execute it on any SQL (2005/2008/R2) server and it will get the proper location and finally show you the details.

I also sorted by date with the most current at the top.

Here it is:

USE [TraceDB] -- change database name to where you keep your stored procs

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DSS_View_Default_Trace]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[DSS_View_Default_Trace]

GO

USE [TraceDB]

GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

CREATE PROC [dbo].[DSS_View_Default_Trace]

as

-- create a temp table to house the data

CREATE TABLE #DTraceLocal (DTraceName NVARCHAR(MAX))

-- inserting and converting data

INSERT INTO #DTraceLocal (DTraceName)

(SELECT CONVERT(NVARCHAR(MAX), [value]) FROM ::fn_trace_getinfo(0))

-- statement below shows actual path of the trace file

--SELECT * FROM #DTraceLocal WHERE [DTraceName] LIKE '%:\%'

-- pick out directory and name of the default trace file

DECLARE @tracelocal NVARCHAR(MAX)

SET @tracelocal = (SELECT * FROM #DTraceLocal WHERE [DTraceName] LIKE '%:\%')

-- showing data from within the default trace file

SELECT

loginname AS 'Login Name',

--spid,

hostname AS 'Host Name',

applicationname AS 'Application Name',

servername AS 'Server Name',

databasename AS 'Database Name',

objectName AS 'Object Name',

--e.category_id AS 'Category ID',

cat.name AS 'Category Name',

textdata AS 'Text Data',

starttime AS 'Start Time',

--eventclass AS 'EventClass',

eventsubclass AS '0=Begin,1=Commit',

e.name AS 'Event Name'

FROM ::fn_trace_gettable(@tracelocal,0)

INNER JOIN sys.trace_events e

ON eventclass = trace_event_id

INNER JOIN sys.trace_categories AS cat

ON e.category_id = cat.category_id

ORDER BY StartTime DESC

-- deleting temp table

DROP TABLE #DTraceLocal

GO