There are one way to setup a trace programmatically but 2 ways to acquire and consume the trace data. To setup a trace, you need to to call trace supporting procedures to define events and columns. If you specify the location of the trace target, SQL Server will write trace data to files. By this approach, the SQL Server instance which reads the data files need to have file access permission to them. The another approach is that you don’t specify the target location. By this approach, the trace data will be “streamed” to the client directly.
First, let’s run the query posted previously to find out the events and columns we want to keep track of. For demo purpose, I chose SQL:BatchStarting event with columns DatabaseID, SPID, and Text.
select a.name Category, b.trace_event_id EventID, b.name EventName, d.trace_column_id FieldID, d.name FieldName, d.type_name Type, d.max_size MaxSize
from sys.trace_categories a
inner join sys.trace_events b on a.category_id = b.category_id
inner join sys.trace_event_bindings c on c.trace_event_id = b.trace_event_id
inner join sys.trace_columns d on d.trace_column_id = c.trace_column_id
where b.name = 'SQL:BatchStarting'
and d.trace_column_id in (1,3,12)
order by a.name, b.name, d.trace_column_id
/*
Category EventID EventName FieldID FieldName Type MaxSize
----------------- -------------------------- ----------------------------
TSQL 13 SQL:BatchStarting 1 TextData text 1073741824
TSQL 13 SQL:BatchStarting 3 DatabaseID int 4
TSQL 13 SQL:BatchStarting 12 SPID int 4
(3 row(s) affected)
*/
Next, you can call sp_trace_create to create a trace and use sp_trace_setevent to setup the events and columns as showing below:
set nocount on
if OBJECT_ID('tempdb..#1') is not null
drop table #1
create table #1(TraceID int)
exec xp_cmdshell N'del c:\temp\abc.trc', no_output
go
declare @TraceID int
exec sp_trace_create @traceid = @TraceID output, @options = 2, @tracefile = N'c:\temp\abc'
insert into #1 values(@TraceID) -- save the traceid that will be used later
exec sp_trace_setevent @traceid = @TraceID, @eventid = 13, @columnid = 1, @on = 1
exec sp_trace_setevent @traceid = @TraceID, @eventid = 13, @columnid = 3, @on = 1
exec sp_trace_setevent @traceid = @TraceID, @eventid = 13, @columnid = 12, @on = 1
exec sp_trace_setstatus @traceid = @TraceID, @status = 1 -- start the trace
go
exec ('--print')
go
declare @TraceID int
select @TraceID = TraceID from #1
exec sp_trace_setstatus @traceid = @TraceID, @status = 0 -- stop the trace
exec sp_trace_setstatus @traceid = @TraceID, @status = 2 -- close the trace
go
select TextData, DatabaseID, SPID from fn_trace_gettable('c:\temp\abc.trc', default)
TextData DatabaseID SPID
----------------------------------------------------------------------------------------- -----------
NULL NULL NULL
NULL NULL NULL
exec ('--print') 34 52
declare @TraceID int
select @TraceID = TraceID from #1
exec sp_trace_setstatus @traceid = @TraceID, @status = 0 -- stop the trace
exec sp_trace_setstatus @traceid = @TraceID, @status = 2 -- close the trace 34 52
NULL NULL NULL
Optionally, you can setup the filter by using sp_trace_setfilter. It will not be demonstrated here. Whenever you need to load the trace data, you will have to call fn_trace_gettable to read the data from the file with sufficient permissions to access the trace files.
You may be very familiar with SQL Profiler. It’s implemented by utilizing the same set of procedures introduced previously. However, it shows the trace data directly on the screen from SQL Server rather than reading it from files. see the code below
if OBJECT_ID('tempdb..#1') is not null
drop table #1
go
create table #1(ColumnID int, Length int, Data varbinary(max))
go
declare @TraceID int
exec sp_trace_create @traceid = @TraceID output, @options = 1
select @TraceID
exec sp_trace_setevent @traceid = @TraceID, @eventid = 13, @columnid = 1, @on = 1
exec sp_trace_setevent @traceid = @TraceID, @eventid = 13, @columnid = 3, @on = 1
exec sp_trace_setevent @traceid = @TraceID, @eventid = 13, @columnid = 12, @on = 1
exec sp_trace_setstatus @traceid = @TraceID, @status = 1 -- start the trace
insert into #1
exec sp_trace_getdata @TraceID, 3
exec sp_trace_setstatus @traceid = @TraceID, @status = 0 -- stop the trace
exec sp_trace_setstatus @traceid = @TraceID, @status = 2 -- close the trace
select e.*,
a.name Category, b.trace_event_id EventID, b.name EventName, d.trace_column_id FieldID, d.name FieldName, d.type_name Type, d.max_size MaxSize
from sys.trace_categories a
inner join sys.trace_events b on a.category_id = b.category_id and b.name = 'SQL:BatchStarting'
inner join sys.trace_event_bindings c on c.trace_event_id = b.trace_event_id
inner join sys.trace_columns d on d.trace_column_id = c.trace_column_id
right join #1 e on e.ColumnID = d.trace_column_id
-- this script will wait until receive a the first trace record
Line 7, you will need to define the trace little bit differently
Line 14, you need to use sp_trace_getdata to receive the trace data generated by SQL Server directly. The first paramenter of this procedure is trace id. The second parameter is number of records you want to receive at a time. If there are not as many records can be received, this procedure will block current caller session.
Line 18, links the captured data in the temp table to metadata. you can use this query to interpret the binary you catched. You will see there are some trace columns does not have meta data. Look like they are all related to composing a trace file. I will do some investigation and post it here.
What if you call sp_trace_getdata in Dot Net by SqlCommand object and open a data reader to receive the trace data, well, you are writing SQL Profiler.