Get Information about SQL Trace
Get Information about SQL Trace
By : Kasim Wirama, MCDBA
SQL Server has many tools for monitoring its performance. One of the most used tools is profiler. Actually profiler engine uses SQL Trace. In this article, I would show you how you get information from your running trace.
1. Catalog view sys.traces You can see all SQL traces running on your SQL Server, by issuing this SQL command against catalog view below :
SELECT status, path, max_size, buffer_count, buffer_size, event_count, dropped_event_count FROM sys.traces;
2. Function fn_trace_geteventinfo This result of the function gives information about list of event and column that a trace is currently running. A trace is identified by trace id, for example if you have trace id is 2, you can get information from the function as below :
SELECT * FROM fn_trace_geteventinfo(2);
3. Function fn_trace_getfilterinfo If you apply filtering on profiler or SQL trace, you see information from the function with input parameter SQL trace id as the following example :
SELECT * FROM fn_trace_getfilterinfo(2);
4. Function fn_trace_gettable If you want to have get profiler/SQL trace while running, you can see information by querying the function.
For example if you put your trace file to C:\mytrace.trc, you issue the following query :
SELECT * FROM fn_trace_gettable (‘c:\mytrace.trc’,1);
5. Dynamic management view : sys.trace_events and sys.trace_columns Sys.trace_events contains list of eventname together their id, so does sys.trace_columns for column information.
Finally, if you run SQL trace, remember to stop and remove the SQL trace from SQL Server, otherwise your disk space is still claimed by SQL trace or your disk space will be entirely filled up. To stop SQL trace, supply value 0 to second input parameters and to remove SQL trace from SQL Server, supply value 2 to second input parameters. for example, if your running trace id is 3, here is the query command to stop and to remove respectively
EXEC sp_trace_setstatus 3,0
EXEC sp_trace_setstatus 3,2