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:

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

About Nick Xu

Not a DBA anymore
This entry was posted in SQL Server, T-SQL Script and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s