Allen White

Use Trace to capture Production Server Activity

One of the great things about SQL Server is that there are many ways to accomplish the same task.

When problems arise it can be difficult to determine what went wrong, so I keep a trace going on all my production servers. I capture details on all RPC Completed and Batch Completed events, so I know what was submitted to the server for processing and when, so when an application problem arises I can see what the application sent to SQL Server, and can work through the issue with the developers or vendor (for third-party apps).

Now, SQL Server Profiler has a great graphical interface that allows you to select what information to trace, but it can seriously impact performance and on a production server that can be a very bad thing. Profiler has a great option that allows you to script the trace commands to a script file. You can then run the script, and you get the same trace you would get without incurring the performance overhead you'd get by running Profiler.

First, set up the trace you want to run in Profiler. You'll want to select the Save To File option, and specify a file name on the server hard drive. (I use a subdirectory under my Backup directory, so the nightly backup to tape grabs the trace files as well.) The default file size is 5MB, but I set it at 20MB so I don't get too many physical files, but I do set it to roll over so I don't lose any trace data.

Once you've set up the options you want you can go to the File menu, select Export, then Script Trace Definition, then For SQL Server 2005, and specify a file name for the script. It'll produce a file something like this:

/****************************************************/
/* Created by: SQL Server Profiler 2005             */
/* Date: 08/28/2007  08:41:47 AM         */
/****************************************************/


-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 20

-- 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, 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, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
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


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

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - fab17603-e5cc-4107-80a1-65330cd2eece'
-- 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

Read the comments, and set up the file name for the trace file appropriately. You can close Profiler because you no longer need it. You can run the script in a new query in SQL Server Management Studio, or you can set up a job to start it up. Once it's running it'll save the server activity you selected earlier in a trace file, which you can then review after something unusual has occurred to see what was sent to the server.

Allen