The default trace is a system-defined trace which resides in SQL Server directory ,which consists of 5 files with the size of 20 MB each one.
Below one is the script to find out the path of default trace where it is running or saved.
SELECT * FROM fn_trace_getinfo(default);
| traceid | property | value | |
| 1 | 1 | 2 | Configured trace options |
| 1 | 2 | C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Log\log_16.trc | Trace file name |
| 1 | 3 | 20 | Max file size for the *.trc file |
| 1 | 4 | NULL | Stop time for the trace session |
| 1 | 5 | 1 | Current trace status (1 = On and 0 = Off) |
After get the default trace path info, use the below query to fetch the trace information.
SELECT * FROM ::fn_trace_gettable(‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Log\log_16.trc’, default)
selectstarfromyogi 