Database Simplified Headline Animator

Database Simplified

Thursday 13 October 2011

Server Side Trace In SQL Server

Running SQL Server Profiler trace are considered to be very costly operation. We generally minimize the load of Profiler trace by filtering the columns and limiting the event in trace. Alternatively we have “Server Side Trace” The Server site trace provides most effective way to trace then SQL Profiler. Server side trace runs on Server so no network congestion takes place. Server side trace runs in background so user need not to worry about keeping trace running on machine all the time as we do with SQL Profiler.

How To Run Server Side Trace

  • Start SQL Server Profiler
    • Choose All the Event and Information Required To Trace.
    • Click Run


  • Goto File Menu –> Export –>Script Trace Definition and Choose Appropriate Version Script Option.
    • Save To Script To File
    • Stop SQL Profiler
    • Close The SQL Profiler


  • Open The File Where Script Was Saved Copy The Script and Paste To Query Window. See Following Script For Example
    • You Must Write You FileName with Path In Place Of “InsertFileNameHere” In Below Script.
    • You can also set value for variable “@maxfilesize” which sets maximum trace file sixe in MB.
    • Now Execute the The Script


/* Created by: SQL Server 2008 R2 Profiler          */
/* Date: 10/14/2011  00:06:22 AM         */
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 50
-- 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 shareexec @rc = sp_trace_create @TraceID output, 2, N'InsertFileNameHere', @maxfilesize, NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 12, @on

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 1f53f9c2-0fe2-4eac-9371-4eb6c496d329'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
select ErrorCode=@rc

In the Above Query You can see the line

exec @rc = sp_trace_create @TraceID output, 2, N'InsertFileNameHere', @maxfilesize, NULL

Where 2 means file will be rollover once file has been filled up. mean you will have multiple sets of @maxfilesize defined. If you don’t mention 2 then trace will be stopped when file size reaches @maxfilesize Defined.
So once trace is started you can monitor trace info with following query. which gives you information about trace.

select * from  fn_trace_getinfo(default)
select * from sys.traces

Output :


As you can see above screen show show traceid and trace filename. you can view the trace using following function.


The trace is keeps on running in the background until it is disabled or stopped by user or server is stopped.

How to Disable or Stop Trace ?

Exec sp_trace_setstatus @traceId=1,@Enabled=0 --To Disable Trace
Exec sp_trace_setstatus @traceId=1,@Enabled=1 --To Enable Trace
Exec sp_trace_setstatus @traceId=1,@Enabled=2 –To Stop Trace

You Must Disable Trace Inorder To Stop A Trace. Once You Have Stopped the Trace Following Query Will Not Return Any Row For Stopped Trace.

Select * from sys.traces

No comments:

Post a Comment