Default Trace SQL Server

SQL Server runs a trace in background which is known as default trace, It helps administrator to troubleshoot few issues. It is enabled by default, we can disable if required, but it is not a overhead for the server because it is light weight.

To View default trace file location

SELECT * FROM fn_trace_getinfo(default);
SELECT * FROM sys.configurations WHERE name = 'default trace enabled'

To disable the default trace file

EXEC master.dbo.sp_configure 'allow updates', 1;

GO

EXEC master.dbo.sp_configure 'show advanced options', 1;

GO

EXEC master.dbo.sp_configure 'default trace enabled', 0;

GO

RECONFIGURE WITH OVERRIDE;

  

To Read Trace file from T-SQL

Select * FROM sys.fn_trace_gettable('FileName', DEFAULT ) * FROM sys.fn_trace_gettable('FileName', DEFAULT )