LLI Wiki — My Digital Knowledge Base

Read the SQL Server default trace events

Query that reads the SQL Server default trace with sys.fn_trace_gettable and shows the most recent system events.

The default trace writes system events of the instance to a file. The following query reads that file with sys.fn_trace_gettable. The file path is taken from sys.fn_trace_getinfo (property 2), and the query joins the event and subclass names.

SELECT TE.name AS [EventName] ,
   v.subclass_name ,
   T.DatabaseName ,
   t.DatabaseID ,
   t.NTDomainName ,
   t.ApplicationName ,
   t.LoginName ,
   t.SPID ,
   t.StartTime ,
   t.RoleName ,
   t.TargetUserName ,
   t.TargetLoginName ,
   t.SessionLoginName
FROM  sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1
                     f.[value]
                  FROM  sys.fn_trace_getinfo(NULL) f
                  WHERE f.property = 2
                  )), DEFAULT) T
   JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
   JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id
               AND v.subclass_value = t.EventSubClass

order by StartTime desc

Notes

Mastodon LLIMastodon SocialFosstodon