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
).
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.
