SQL tracing in general, and server-side tracing in particular (simply because of its much lower overhead) is one of my favorite tools for a wide variety of tasks ranging from auditing, performance tuning, lock and deadlock investigations to looking under the hood at how a particular application is accessing a system, or even how some of the SQL tools perform their tasks. Creating SQL traces using the sp_trace_create proc can be a little tricky for a first timer, but creating your own scripts or procedures gives you much more control over how a trace works then using the profiler, so I highly recommend you give it a shot.

Here's an example script that I frequently use as a starting point:

declare @ProcReturn int,

@TraceID int

exec@ProcReturn = sp_trace_create

@TraceID = @TraceID out,

@Options = 2,

@TraceFile = N'e:\TraceData\LockingIssues',

@MaxFileSize = null

select@ProcReturn,

@TraceID

declare@Column int,

@On bit

set@Column = 1

set@On = 1

while@Column <= 44 begin

execsp_trace_setevent @TraceID = @TraceID, @EventID = 10, @ColumnID = @Column, @On = @On

execsp_trace_setevent @TraceID = @TraceID, @EventID = 12, @ColumnID = @Column, @On = @On

execsp_trace_setevent @TraceID = @TraceID, @EventID = 41, @ColumnID = @Column, @On = @On

execsp_trace_setevent @TraceID = @TraceID, @EventID = 43, @ColumnID = @Column, @On = @On

execsp_trace_setevent @TraceID = @TraceID, @EventID = 50, @ColumnID = @Column, @On = @On

execsp_trace_setevent @TraceID = @TraceID, @EventID = 59, @ColumnID = @Column, @On = @On

set@Column = @Column + 1

end

exec@ProcReturn = sp_trace_setstatus

@TraceID = @TraceID,

@status = 1

select@ProcReturn,

@TraceID

This example captures all of the SQL 2000 columns for these events:

RPC: Completed, SQL: BatchCompleted, SQL: StmtCompleted, SP: Completed, SQL Transaction, and Lock: Deadlock Chain.

You can examine the contents of the trace after you've stopped it by using this select:

select*

from::fn_trace_gettable('e:\TraceData\LockingIssues.trc', default)

whereEndTime between '2007-11-07 07:56:00.000' and '2007-11-07 08:05:00.000'

Regards,

Eric