Best Way to Set up a Server Side Trace

Accidentally bumped into this tutorial about setting up server side trace. By using SQL profile and scripting our the trace definition, you’d save a lot of work by defining various events that you’d like to trace. I am taking a note here for future reference.

Some other useful T-SQL commands that involve the control of server side tracing are:
[sourcecode language=”sql”]
–To get the list of currently running traces
SELECT * FROM ::fn_trace_getinfo(DEFAULT)

–To get the list of all traces (running or not)
SELECT * FROM sys.traces

–To pause a running trace
EXEC sp_trace_setstatus @traceid = <traceid> , @status = 0

–To close and delete a trace
EXEC sp_trace_setstatus @traceid = <traceid> , @status = 2
[/sourcecode]