SQL Server Server Side Trace

Often I get request by my clients to run SQL traces in odd hours for performance analysis, SQL Traces are resource expensive operation and we have to be careful how we run the SQL Profiler.

It is not recommended to run SQL Trace through SQL profiler GUI for a long time over the network, You may end up creating performance issues with the server you are running the trace for,
The most effective way to run SQL traces is running a Server side trace and most importantly we can automate this and schedule it as when required though SQL Agent. Let us see in this article
we can run a server side SQL trace.

First thing we need to prepare for a server side trace is the trace template, It can be easily prepared for your events and columns using SQL Profiler as below,

Select the event and columns you require in the trace,

 

Add the appropriate column filter to reduce the unwanted data in the trace,

 

 

Now Run the trace for couple of seconds and stop it but do not close the window. Go To File->Export->Script Trace Definition->For SQL Server 2005 – SQL11…  and save the template as .SQL file locally.

 

 

Now Open the trace definition file, edit the sp_trace create parameters to give the output trace file names and rollover parameters (http://technet.microsoft.com/en-us/library/ms190362.aspx ) and save the file.

 

/****************************************************/

/* Created by: SQL Server 2012  Profiler          */

/* Date: 14/11/2013  10:44:08         */

/****************************************************/

-- Create a Queue

declare @rc int

declare @TraceID int

declare @maxfilesize bigint

set @maxfilesize = 5

-- Please replace the text InsertFileNameHere, with an appropriate

-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension

-- will be appended to the filename automatically. If you are writing from

-- remote server to local drive, please use UNC path and make sure server has

-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N'D:\Vinoth\TestServer_SQLTrace', 20, NULL

if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

If you note at the bottom of the trace definition you may find the filters you have added in the GUI and a store procedure exec sp_trace_setstatus @TraceID, 1.

This procedure is used to start/stop and delete traces automatically (http://technet.microsoft.com/en-us/library/ms176034.aspx ).

 Now that we have set up the template, we can copy the trace definition create a SQL Agent Job. This Job will start the trace you have define on the server to the trace file as provided in the definition.

Use the below system table to find if the trace has started successfully,

select * from sys.traces

we have now set a Job to start the trace successfully we can schedule the job as per the requirement. Let us now look into how we can stop the trace in an automated way.I use the below TSQL to stop your server side trace using the sp_trace_setstatus store proc again with the stop parameter.

DECLARE @tcid int

 

if exists(select *  from sys.traces where path like '%<your Trace Definition base path>%')

BEGIN

select @tcid = id from sys.traces where path like '%<your Trace Definition base path>%'

exec sp_trace_setstatus @tcid, 0

exec sp_trace_setstatus @tcid, 2

END

 

You can set up this code in a SQL Agent Job again and Schedule it to run when you want to stop the trace thus making the whole process automated without manual intervention and running as server side trace takes much less resource to SQL Profiler.