INF: How to Create a SQL Server 7.0 Trace (289742)

trace_build7  @traceini
				

MORE INFORMATION

The following example creates a trace from information contained in the Trace1.ini file:

trace_build 'c:\Trace1.ini'
				

The @traceini file must contain the following information:

• @tracefile
  
  Data is written to the trace file specified in this @tracefile variable. Change @tracefile to save the trace to a different drive and file name. Note that a .trc extension is automatically added by the trace.
• @events
  
  Change @events to control the events that are traced.

Run the following statement in a query window to get a list of event names and event class numbers:

master..xp_trace_geteventnames
				

For example, consider the following contents of a @traceini file that is named TraceArgs.ini:

@tracefile         = C:\ActivityTrace7 
@events            = 10,11,12,13,16,17,19,33,42,43,55 
				

When the following statement is executed:

trace_build7 'C:\TraceArgs.ini'
				

The script creates a tracefile, C:\ActivityTrace7.trc (@tracefile), which contains the trace. The following events are traced (@events):

• RPC:Completed
• RPC:Starting
• SQL:BatchCompleted
• SQL:BatchStarting
• Attention
• ExistingConnection
• DTCTransaction
• Exception
• SP:Starting
• SP:Completed
• Hash Warning

All the columns for the preceding events are traced.

For more information about stopping or checking the status of the trace, see the following article in the Microsoft Knowledge Base: Trace_build7 Stored Procedure

CREATE proc trace_build7  @traceini nvarchar (245) = N'C:\ActivityTrace7.ini' as 
set nocount on
declare @traceid int, @tracefile nvarchar (245), @rc int, @cmd1 nvarchar(512)
      , @events varchar(512), @event int, @estart int, @enext int, @le int
create table #t1 ([c1] nvarchar(512))
set @cmd1 = 'bulk insert #t1 FROM '''
exec (@cmd1 + @traceini + '''')
select @tracefile = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245)) from #t1 where left(c1,3) = '@tr'
select @events=cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (512)) from #t1 where left(c1,3) = N'@ev'
set @traceid = 0
create table #traces (traceid int)
insert #traces EXEC master.dbo.xp_trace_enumqueuehandles

create table #_dest (Destination varchar(256)
                  , [On] int, Server sysname NULL, Object sysname NULL)
while (select top 1 count(*) from #traces) > 0
 begin
  select top 1 @traceid = traceid from #traces
  insert #_dest exec master.dbo.xp_trace_getqueuedestination @traceid
  if (select top 1 count(*) from #_dest where object = @tracefile+'.trc') > 0
   begin
   select 'Cannot create new trace for same destination,' + object
          + ', as trace number ' + cast(@traceid as varchar(3)) as [Destination]
     from #_dest where object is not null
   goto finish
   end
  truncate table #_dest
  delete #traces where traceid = @traceid
	end
drop table #_dest
drop table #traces
set @cmd1 = 'if exist ' + @tracefile + '.trc ' + 'del ' + @tracefile + '.trc'
exec @rc = master.dbo.xp_cmdshell @cmd1, no_output
EXEC master.dbo.xp_trace_addnewqueue 3000, 6000, 95, 90, 129761279, @traceid OUTPUT
select @estart = 1
select @enext = charindex(',',@events,@estart)
set @le = len(@events)
while @enext > 0
 begin
 select @event = cast(substring(@events,@estart,@enext-@estart) as int)
 EXEC master.dbo.xp_trace_seteventclassrequired @traceid, @event, 1
 select @estart = @enext + 1
 select @enext = charindex(',',@events,@estart)
 if @enext = 0 set @enext = @le + 1
 if @estart > @le set @enext = 0
 end
set @tracefile = @tracefile + '.trc'
EXEC master.dbo.xp_trace_setqueuedestination @traceid, 2, 1, NULL,@tracefile
EXEC master.dbo.xp_trace_startconsumer @traceid
finish:
drop table #t1
set nocount off
				

REFERENCES

SQL Server 7.0 Books Online; topics: "xp_trace_seteventclassrequired (T-SQL)"; "xp_trace_eventclassrequired (T-SQL)"; "xp_trace_geteventnames (T-SQL)"