INF: How to Monitor SQL Server 7.0 Traces

This article was previously published under Q289279
This article has been archived. It is offered "as is" and will no longer be updated.
Summary
You can use the trace7 stored procedure to obtain information about the trace destination and events for one or all traces. You can use trace7 to stop and delete one or all traces.
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 onif @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 endcreate table #traces (traceid int)insert #traces EXEC master.dbo.xp_trace_enumqueuehandlesif (select count(*) from #traces) < 1 begin  select 'No traces exist.' as [Trace Information]  goto finish endif @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 endif (@setstatus = 0) goto changestatuscreate 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	endgoto finishchangestatus:if @traceid > 0 begin  EXEC master.dbo.xp_trace_destroyqueue @traceid 	select 'Trace number ' + cast(@traceid as varchar(3)) + ' is stopped.' as [Trace Information] endelse 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 endfinish:set nocount off				
NOTE: Execute trace7 -1 in a query window to see the syntax for the stored procedure.

profiler
Properties

Article ID: 289279 - Last Review: 11/02/2013 01:37:00 - Revision: 2.0

  • Microsoft SQL Server 7.0 Standard Edition
  • kbnosurvey kbarchive kbDSupport kbinfo KB289279
Feedback