Note: While SQL Profiler is not included with SQL Server Express, it is included in the Developer version, which sells for about $50 (and I’ve seen slightly better prices on Amazon).
1. Create a trace. Open SQL Server Management Studio and execute the Transact-SQL below. Note that the sp_trace_create stored procedure is used to create a trace (see the sp_trace_create documentation for a detailed description of the stored procedure and its parameters). The code below will create a trace file (Trace.trc) in the C:\Users\Public\Documents folder. The .trc extension will be appended automatically to the trace file name. The file should not already exist.
/* Declare variables used in trace creation */
declare @rc int -- Return Code for sp_trace_create
declare @TraceID int -- Trace ID created by sp_trace_create
declare @maxfilesize bigint -- Max size in MB for trace file
set @maxfilesize = 5
/* Create the trace. */
exec @rc = sp_trace_create @TraceID output,
0,
N'C:\Users\Public\Documents\Trace',
@maxfilesize,
NULL
2. Set the events to be monitored. The hardest part in setting up a trace is in deciding what information you want to collect. The sp_trace_setevent stored procedure is used to set the events to be monitored (see the sp_trace_setevent documentation for more info). If you follow that link to the documentation you’ll see that there is a long list of events that you can collect information about. And, for each event, you have to decide what information you want to collect (see the @columnid parameter in the documentation). To keep things relatively simple, the Transact-SQL below collects some information for the RPC:Starting, RPC:Completed, SQL:BatchStarting,
Read more: Brian Swan
Posts
