How to read and use default trace in SQL Server
Default trace in SQL will play a very good role in troubleshooting purpose for a DBA.How to check whether default trace is enabled or not
Run the query:sp_configure
search for "default trace enabled" name in the output, if config value and run value is "1" then default trace is enabled.
If it is '0' then default trace is disabled.
How to enabled the default trace:
sp_configure 'show advanced options',1
Go
Reconfigure
Go
sp_congure 'default trace enabled',1
Go
Reconfigure
Thats it. Default trace is now enabled.
To find the properties and log path of trace
select * from ::fn_trace_getinfo(0)
Log file name with path will be provided in the output, use the same in the next query to read the trace file.
Limit for the number of trace files is 5.
Reading the trace file:
SELECT * FROM fn_trace_gettable('d:\Program Files\Microsoft SQL Server\MSSQL\log\log_126.trc', default)
Above query will read the trace file.
Hope the above information helps
Thanks
