Creating trace files is an efficient way to quickly store trace data by using minimal system resources. We can store trace data into a trace table on another server, but this process is a heavy user of system resources.
SQL Server runs a default server-side trace that is started automatically whenever SQL Server starts.
The trace is called default trace and has the trace ID of 1.
- it is a lightweight trace that keeps up to 5 MB of data at any point in time
- it captures key SQL Server events such as database growth and objects being created or dropped
- it can be enabled or disabled by using sp_configure
- it cannot be modified
- its default file location cannot be changed
- it provides troubleshooting assistance to database administrators by ensuring that they have the log data necessary to diagnose problems the first time they occur
- it is also used by the Configuration Changes History report in SQL Server Management Studio
IN A NUT SHELL
- SQL Trace exposes events that can be captured to audit actions, monitor an instance, examine baseline queries, and troubleshoot performance issues.
- The SQL Trace exposes events that can be captured to audit actions, monitor an instance, examine baseline queries, and troubleshoot performance issues.
- We can specify the columns of data we want to capture for a given event.
- Trace output can be limited by applying filters
- Use SQL Trace for large and long-running traces
- Use SQL Server Profiler to define traces and then script them for SQL Trace
The trace event used to determine when a T-SQL statement has completed are:
- SQL:StmtCompleted indicates that a T-SQL statement completed.
- RPC:Completed indicates a remote procedure call (stored procedure) completed.