The Server-side Trace

When you open SQL Server Profiler and run a trace, it is always a client-side trace. To run a server-side trace, you need to create a script, you can do it from SQL Server Profiler:

- create a new trace in SQL Server Profiler and Select Save to File (file location is not important).
- select Enable File Rollover and Enable Stop Time (we will change actual time later in the script).
- choose events from the Events Selection tab.
- run the trace and then stop it immediately.
- from the File menu, choose Export -> Script Trace Definition -> For SQL Server 2005, 2008 or 2000
- open the script file in SQL Server Management Studio, it will look similar to this:


/****************************************************/
/* Created by: SQL Server 2008 R2 Profiler          */
/* Date: 02/05/2012  12:12:06         */
/****************************************************/

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
set @DateTime = '2012-05-02 13:10:28.000'
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'InsertFileNameHere', @maxfilesize, @Datetime
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, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 1a03a9bf-4882-41e5-f76b4df5ed71' --excludes SQL Server Profiler events
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go

By default there is a filter on the ApplicationName column, set to exclude SQL Server Profiler events:

@DateTime is the trace stop time. Edit the stop date and time, or set it to NULL for no stop time, or set a fixed trace duration (for example 1 hour): @DateTime = DateAdd(h, 1, GetDate())

change "InsertFileNameHere", for example, "\\server_name\folder_name\TraceName" or "d:\TraceFolder\TraceName". SQL will append the .TRC extension to the filename you provide.

@option in sp_trace_create is set to 2 for trace file rollover, 4 for shutdown on error, or 6 for both or 8 option for "blackbox" (Specifies that a record of the last 5 MB of trace information produced by the server will be saved by the server) that’s not compatible with the other options.

@maxfilesize is the size of trace output files in MB.

Here is an example of server side trace script, with @option set to 2 for trace file rollover, @maxfilesize set to 50MB, and @filecount set to 8 rollover files,
and @bigintfilter for duration of events of more than 10000 microseconds (In SQL Server 2005 and later, in the SQL Server Profiler you enter duration in milliseconds by default, but @bigintfilter is in microseconds, and the Duration column value in the trace is written in microseconds):

event duration in SQL Server Profiler

/****************************************************/
/* Created by: SQL Server 2008 R2 Profiler */
/* Date: 02/05/2012 12:12:06 */
/****************************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @options int
set @options = 2
set @maxfilesize = 50
declare @filecount int
set @filecount = 8
-- 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, @options, N'D:\trace\testtrace', @maxfilesize, NULL, @filecount
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, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
set @bigintfilter = 10000
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go

The script will create the trace, and start it with: "exec sp_trace_setstatus @TraceID, 1"

With exec sp_trace_setstatus  @traceid =  2,  @status =  0    -- you can stop the trace
With exec sp_trace_setstatus  @traceid =  2,  @status =  2    -- you can delete the trace

@traceid was "2" in these examples, but your trace may have different ID. When you run your trace, the returned value is your trace ID.

To find @traceid, run:
SELECT * FROM :: fn_trace_getinfo(default)
This will give you a list of all of the traces that are running on the server.

The property of the trace as represented by the following integers:

1 - Trace Options (@options in sp_trace_create)
2 - FileName
3 - MaxSize
4 - StopTime
5 - Current Trace status (1 = On and 0 = Off)

To see how to read the trace file using fn_trace_gettable, read related blog post:
How to read SQL Server Trace Files using fn_trace_gettable function