Posted on Leave a comment

SQL Server Default Trace 0


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


--To enable default trace
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'default trace enabled', 1;
GO
RECONFIGURE;
GO


--To query contents of default trace file
SELECT TE.*
     , T.*
  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


More info:

http://www.databasejournal.com/features/mssql/a-few-cool-things-you-can-identify-using-the-default-trace.html

https://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server---the-power-of-performance-and-security-auditing/
Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.