Dec 122011
 

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.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="">

(required)

(required)

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

This site uses Akismet to reduce spam. Learn how your comment data is processed.