Default Trace

Often overlooked, the default trace is there with some useful audit info should you need it.


--see if default trace is enabled
    SELECT * 
      FROM sys.configurations 
     WHERE configuration_id = 1568

--list events that are captured
    SELECT distinct E.name "EventName"
      FROM fn_trace_geteventinfo(1) I
INNER JOIN sts.trace_events E
        ON I.eventid = E.trace_event_id


--path to trace file
    SELECT reverse(substring(reverse(path), charindex('\',reverse(path)), 256)) "default_tracepath"
      FROM sys.traces
     WHERE is_default = 1

--query trace file
    SELECT *
      FROM sys.fn_trace_gettable(convert(varchar(150),
           ( SELECT top 1
               FROM sys.fn_trace_getinfo(NULL) f
              WHERE f.property = 2 )), default ) T
INNER JOIN sys.trace_events E
        ON T.eventclass = E.trace_event_id