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:
--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