Microsoft KB Archive/289279

= INF: How to Monitor SQL Server 7.0 Traces =

Article ID: 289279

Article Last Modified on 4/6/2001

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q289279



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 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.

Additional query words: profiler

Keywords: kbinfo KB289279

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.