INF: How to Monitor SQL Server 7.0 Traces (289279)

MORE INFORMATION

To create the trace7 stored procedure, run the script that follows in the SQL Server Query Analyzer.

CREATE      proc trace7  @traceid int = 0, @setstatus int = -1 as 
set nocount on

if @traceid < 0
 begin
  print 'Syntax for trace stored procedure.'
  print 'trace [@traceid], [@setstatus]'
  print 'The default for @traceid is 0.'
  print 'The default for @setstatus is -1.'
  print '@traceid @setstatus          action'
  print '>0          not 0      list information for one trace'
  print '0           not 0      list information all traces'
  print '>0              0      stop and delete one trace'
  print '0               0      stop and delete all traces'
  print 'Example 1.  Stop and delete all traces.'
  print 'trace 0,0'
  print 'Example 2.  Stop and delete trace number 1.'
  print 'trace 1,0'
  goto finish
 end

create table #traces (traceid int)
insert #traces EXEC master.dbo.xp_trace_enumqueuehandles
if (select count(*) from #traces) < 1
 begin
  select 'No traces exist.' as [Trace Information]
  goto finish
 end

if @traceid > 0
 begin
 delete #traces where [traceid] != @traceid
 if (select count(*) from #traces where [traceid] = @traceid) < 1
  begin
   select 'Trace number ' + cast(@traceid as varchar(3))
          +' does not exist.' as [Trace Information]
   goto finish
  end
 end

if (@setstatus = 0) goto changestatus

create table #_dest (Destination varchar(256)
                  , [On] int, Server sysname NULL, Object sysname NULL)
create table #_evnt (EventName varchar(31), EventRequired int)

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 is not null) > 0
      select 'The trace destination for trace number '
             +cast(@traceid as varchar(3))+' is ' + object as [Destination]
        from #_dest where object is not null
  else select 'No destination for trace number ' + cast(@traceid as varchar(3))
  truncate table #_dest
  insert #_evnt exec master.dbo.xp_trace_eventclassrequired @traceid
  if (select top 1 count(*) from #_evnt where EventRequired > 0) > 0
      select 'Trace number '+cast(@traceid as varchar(3))+' is tracing event'
             +EventName as [Events]
        from #_evnt where EventRequired > 0
  else select 'No events traced for trace number ' + cast(@traceid as varchar(3))
  truncate table #_evnt
  delete #traces where traceid = @traceid
	end

goto finish

changestatus:
if @traceid > 0
 begin
  EXEC master.dbo.xp_trace_destroyqueue @traceid
 	select 'Trace number ' + cast(@traceid as varchar(3)) + ' is stopped.' as [Trace Information]
 end
else
 begin
 while (select top 1 count(*) from #traces) > 0
  begin
   select top 1 @traceid = traceid from #traces
   delete #traces where [traceid] = @traceid
   EXEC master.dbo.xp_trace_destroyqueue @traceid
   select 'Trace number ' + cast(@traceid as varchar(3)) + ' is stopped.' as [Trace Information]
  end
 end
finish:
set nocount off
				

NOTE: Execute trace7 -1 in a query window to see the syntax for the stored procedure.