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
- The default trace can be disabled with the
default trace enabledoption ofsp_configure. If the file does not exist, the query returns no rows. - The query takes the first trace returned by
sys.fn_trace_getinfo. If other traces are running, it may read a file that is not the default trace.