-- Find out if Default Trace is Enabled
SELECT * 
FROM sys.configurations 
WHERE name = 'default trace enabled'

-- Find out if Default Trace is Enabled
EXEC sys.sp_configure 'show advanced options', 1  
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure 'default trace enabled'
GO
EXEC sys.sp_configure 'show advanced options', 0 
RECONFIGURE WITH OVERRIDE


-- How to disabled the Default Trace
EXEC sys.sp_configure 'show advanced options', 1  
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure 'default trace enabled', 0
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure 'show advanced options', 0 
RECONFIGURE WITH OVERRIDE
GO


-- Retrieve trace information.
SELECT *
FROM sys.fn_trace_getinfo(default) 

-- Get the list of Events being collected
SELECT DISTINCT e.name
FROM sys.fn_trace_geteventinfo(1) t
JOIN sys.trace_events e ON t.eventID = e.trace_event_id


-- Query the Default Trace

-- Get Current Filename
DECLARE @filename VARCHAR(MAX) 
SELECT @filename = CAST(value AS VARCHAR(MAX)) 
FROM fn_trace_getinfo(DEFAULT) 
WHERE property = 2 
  AND value IS NOT NULL 

SELECT gt.EventClass, 
       te.Name AS EventName,  
       gt.TEXTData, 
       gt.NTUserName, 
       gt.NTDomainName, 
       gt.HostName, 
       gt.ApplicationName, 
       gt.LoginName, 
       gt.SPID, 
       gt.StartTime, 
       gt.EndTime, 
       gt.ObjectName, 
       gt.DatabaseName, 
       gt.FileName 
FROM sys.fn_trace_gettable(@fileName, DEFAULT) gt 
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id 
ORDER BY StartTime; 

GO
DECLARE @FileName VARCHAR(MAX)  

SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'  
FROM sys.traces   
WHERE is_default = 1;  

SELECT 
	gt.EventClass, 
	te.Name AS EventName,  
	gt.TEXTData, 
	gt.NTUserName, 
	gt.NTDomainName, 
	gt.HostName, 
	gt.ApplicationName, 
	gt.LoginName, 
	gt.SPID, 
	gt.StartTime, 
	gt.EndTime, 
	gt.ObjectName, 
	gt.DatabaseName, 
	gt.FileName 
FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt  
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id 
ORDER BY StartTime; 
