Default Trace Analysis

http://troubleshootingsql.com/2013/09/26/woot-default-trace-and-power-view/







declare @enable int

 

-- Check to find out if Default Server Side traces are running

select top 1 @enable = convert(int,value_in_use) from sys.configurations where name = 'default trace enabled'

 

if @enable = 1 --default trace is enabled

begin

 

declare @d1 datetime;

declare @diff int;

declare @curr_tracefilename varchar(500);

declare @base_tracefilename varchar(500);

declare @indx int ;

 

select @curr_tracefilename = path from sys.traces where is_default = 1 ;

 

set @curr_tracefilename = reverse(@curr_tracefilename)

select @indx = PATINDEX('%\%', @curr_tracefilename)

set @curr_tracefilename = reverse(@curr_tracefilename)

set @base_tracefilename = LEFT( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc';

 

select EventCat.name as Category, EventID.name as EventName, Events.*

from ::fn_trace_gettable( @base_tracefilename, default ) Events

inner join sys.trace_events EventID

on Events.EventClass = EventID.trace_event_id

inner join sys.trace_categories EventCat

on EventID.category_id = EventCat.category_id

 

end