MSSQL Default Trace

Databases

SQL Server

MSSQL Diagnostics & Tuning

Check

Enable

Query

Bibliography

Introduced in SQL2005

Check

SELECT *

FROM sys.configurations

WHERE configuration_id = 1568

SELECT traceid,

value AS [TraceFileName]

FROM ::fn_trace_getinfo(0)

WHERE property=2

Enable

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'default trace enabled', 1;

GO

RECONFIGURE;

GO

Query

SELECT loginname, spid, applicationname, servername, databasename, objectName, e.category_id, cat.name AS [CategoryName], textdata, starttime, e.trace_event_id, CASE eventclass WHEN 46 THEN 'Create' WHEN 47 THEN 'Drop' WHEN 164 THEN 'Alter' ELSE CAST(eventclass AS VARCHAR) END AS [EventClass], CASE eventsubclass WHEN 0 THEN 'Begin' WHEN 1 THEN 'Commit' ELSE CAST(eventsubclass AS VARCHAR) END AS [EventSubClass], e.name AS [EventName] FROM ::fn_trace_gettable(CONVERT(VARCHAR(150), (SELECT value FROM sys.fn_trace_getinfo(0) WHERE traceid=1 AND property=2)), DEFAULT) INNER JOIN sys.trace_events AS e ON eventclass = trace_event_id INNER JOIN sys.trace_categories AS cat ON e.category_id = cat.category_id WHERE databasename = 'myDB' AND objectname IS NULL AND e.category_id = 5 AND e.trace_event_id = 46

eventsubclass0 = Begin1 = Commit

category_id5 = Object8 = Security Audit

trace_event_id 46 = Create 47 = Drop115 =164 = Alter

Bibliography

https://www.brentozar.com/blitz/default-trace-contents/https://www.sqlservercentral.com/articles/default-trace-a-beginners-guidehttps://www.red-gate.com/simple-talk/databases/sql-server/performance-sql-server/the-default-trace-in-sql-server-the-power-of-performance-and-security-auditing/https://dba.stackexchange.com/questions/317631/why-does-fn-trace-gettable-with-the-default-parameter-not-roll-over-all-trace-lo