INF: How to Monitor SQL Server 2000 Traces (283786)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q283786

SUMMARY

This article describes how to create and use a stored procedure called trace to get information on trace status, events, columns, and filters for one or all traces. You can use trace to start, stop, and delete one or all traces.

MORE INFORMATION

The syntax for the trace stored procedure is as follows: 
trace @traceid, @setstatus
where:
  • @traceid is the identification number of the trace. The data type is integer, and the default value is 0.
  • @setstatus specifies the action to implement on the trace. The data type is integer, and the default value is -1.
If you specify a negative number for @traceid, the following table is displayed. This table lists the actions that are taken for the various combinations of values of @traceid and @setstatus.

@traceid@setstatusAction
>0Not 0,1, or 2List information for one trace.
0Not 0,1, or 2List information all traces.
>00Stop one trace.
>01Start one trace.
>02Stop and delete one trace.
00Stop all traces.
01Start all traces.
02Stop and delete all traces.

For example, to stop all traces, issue the following command: 
trace 0,0
To start trace number 1, issue the following command: 
trace 1,1
To stop and delete all traces, issue the following command: 
trace 0,2
When no arguments are specified, the stored procedure returns a list of the trace properties, events, columns, and filters for all traces. For additional information about properties, events, columns, and filters, see the following SQL Server 2000 Books Online articles:
  • fn_trace_getinfo
  • sp_trace_setevent
  • sp_trace_setfilter
To create the trace stored procedure, execute the following script in a Query Analyzer window: 
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE            proc trace  @traceid int = 0, @setstatus int = -1 as 
declare @events table (eventid int, [event] varchar(31))
declare @columns table (columnid int, [column] varchar(31))
declare @traces table (traceid int, [property] int, [tpdesc] nvarchar(245), check1 bit)
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,1, or 2	list information for one trace'
print '0		not 0,1, or 2	list information all traces'
print '>0		0		stop one trace'
print '>0		1		start one trace'
print '>0		2		stop and delete one trace'
print '0		0		stop all traces'
print '0		1		start all traces'
print '0		2		stop and delete all traces'
print 'Example 1.  Stop all traces.'
print 'trace 0,0'
print 'Example 2.  Start trace number 1.'
print 'trace 1,1'
print 'Example 3.  Stop and delete all traces.'
print 'trace 0,2'
goto finish
end
Insert @events values (10,'RPC:Completed')
Insert @events values (11,'RPC:Starting')
Insert @events values (12,'SQL:BatchCompleted')
Insert @events values (13,'SQL:BatchStarting')
Insert @events values (14,'Login')
Insert @events values (15,'Logout')
Insert @events values (16,'Attention')
Insert @events values (17,'ExistingConnection')
Insert @events values (18,'ServiceControl')
Insert @events values (19,'DTCTransaction')
Insert @events values (20,'Login Failed')
Insert @events values (21,'EventLog')
Insert @events values (22,'ErrorLog')
Insert @events values (23,'Lock:Released')
Insert @events values (24,'Lock:Acquired')
Insert @events values (25,'Lock:Deadlock')
Insert @events values (26,'Lock:Cancel')
Insert @events values (27,'Lock:Timeout')
Insert @events values (28,'DOP Event')
Insert @events values (33,'Exception')
Insert @events values (34,'SP:CacheMiss')
Insert @events values (35,'SP:CacheInsert')
Insert @events values (36,'SP:CacheRemove')
Insert @events values (37,'SP:Recompile')
Insert @events values (38,'SP:CacheHit')
Insert @events values (39,'SP:ExecContextHit')
Insert @events values (40,'SQL:StmtStarting')
Insert @events values (41,'SQL:StmtCompleted')
Insert @events values (42,'SP:Starting')
Insert @events values (43,'SP:Completed')
Insert @events values (44,'Reserved ')
Insert @events values (45,'Reserved ')
Insert @events values (46,'Object:Created')
Insert @events values (47,'Object:Deleted')
Insert @events values (48,'Reserved')
Insert @events values (49,'Reserved')
Insert @events values (50,'SQL Transaction')
Insert @events values (51,'Scan:Started')
Insert @events values (52,'Scan:Stopped')
Insert @events values (53,'CursorOpen')
Insert @events values (54,'Transaction Log')
Insert @events values (55,'Hash Warning')
Insert @events values (58,'Auto Update Stats')
Insert @events values (59,'Lock:Deadlock Chain')
Insert @events values (60,'Lock:Escalation')
Insert @events values (61,'OLE DB Errors')
Insert @events values (67,'Execution Warnings')
Insert @events values (68,'Execution Plan')
Insert @events values (69,'Sort Warnings')
Insert @events values (70,'CursorPrepare')
Insert @events values (71,'Prepare SQL')
Insert @events values (72,'Exec Prepared SQL')
Insert @events values (73,'Unprepare SQL')
Insert @events values (74,'CursorExecute')
Insert @events values (75,'CursorRecompile')
Insert @events values (76,'CursorImplicitConversion')
Insert @events values (77,'CursorUnprepare')
Insert @events values (78,'CursorClose')
Insert @events values (79,'Missing Column Statistics')
Insert @events values (80,'Missing Join Predicate')
Insert @events values (81,'Server Memory Change')
Insert @events values (82,'User Configurable')
Insert @events values (83,'User Configurable')
Insert @events values (84,'User Configurable')
Insert @events values (85,'User Configurable')
Insert @events values (86,'User Configurable')

Insert @events values (87,'User Configurable')
Insert @events values (88,'User Configurable')
Insert @events values (89,'User Configurable')
Insert @events values (90,'User Configurable')
Insert @events values (91,'User Configurable')
Insert @events values (92,'Data File Auto Grow')
Insert @events values (93,'Log File Auto Grow')
Insert @events values (94,'Data File Auto Shrink')
Insert @events values (95,'Log File Auto Shrink')
Insert @events values (96,'Show Plan Text')
Insert @events values (97,'Show Plan ALL')
Insert @events values (98,'Show Plan Statistics')
Insert @events values (99,'Reserved')
Insert @events values (100,'RPC Output Parameter')
Insert @events values (101,'Reserved')
Insert @events values (102,'Audit Statement GDR')
Insert @events values (103,'Audit Object GDR')
Insert @events values (104,'Audit Add/Drop Login')
Insert @events values (105,'Audit Login GDR')
Insert @events values (106,'Audit Login Change Property')
Insert @events values (107,'Audit Login Change Password')
Insert @events values (108,'Audit Add Login to Server Role')
Insert @events values (109,'Audit Add DB User')
Insert @events values (110,'Audit Add Member to DB')
Insert @events values (111,'Audit Add/Drop Role')
Insert @events values (112,'App Role Pass Change')
Insert @events values (113,'Audit Statement Permission')
Insert @events values (114,'Audit Object Permission')
Insert @events values (115,'Audit Backup/Restore')
Insert @events values (116,'Audit DBCC')
Insert @events values (117,'Audit Change Audit')
Insert @events values (118,'Audit Object Derived Permission')

insert @columns values (1,'TextData')
insert @columns values (2,'BinaryData')
insert @columns values (3,'DatabaseID')
insert @columns values (4,'TransactionID')
insert @columns values (5,'Reserved')
insert @columns values (6,'NTUserName')
insert @columns values (7,'NTDomainName')
insert @columns values (8,'ClientHostName')
insert @columns values (9,'ClientProcessID')
insert @columns values (10,'ApplicationName')
insert @columns values (11,'SQLSecurityLoginName')
insert @columns values (12,'SPID')
insert @columns values (13,'Duration')
insert @columns values (14,'StartTime')
insert @columns values (15,'EndTime')
insert @columns values (16,'Reads')
insert @columns values (17,'Writes')
insert @columns values (18,'CPU')
insert @columns values (19,'Permissions')
insert @columns values (20,'Severity')
insert @columns values (21,'EventSubClass')
insert @columns values (22,'ObjectID')
insert @columns values (23,'Success')
insert @columns values (24,'IndexID')
insert @columns values (25,'IntegerData')
insert @columns values (26,'ServerName')
insert @columns values (27,'EventClass')
insert @columns values (28,'ObjectType')
insert @columns values (29,'NestLevel')
insert @columns values (30,'State')
insert @columns values (31,'Error')
insert @columns values (32,'Mode')
insert @columns values (33,'Handle')
insert @columns values (34,'ObjectName')
insert @columns values (35,'DatabaseName')
insert @columns values (36,'Filename')
insert @columns values (37,'ObjectOwner')
insert @columns values (38,'TargetRoleName')
insert @columns values (39,'TargetUserName')
insert @columns values (40,'DatabaseUserName')
insert @columns values (41,'LoginSID')
insert @columns values (42,'TargetLoginName')
insert @columns values (43,'TargetLoginSID')
insert @columns values (44,'ColumnPermissionsSet')

insert into @traces 
 SELECT [traceid], [property]
      , [tpdesc]  = 
	case
	when [property] = 1 and [value] = 1 then N' produces a rowset.'
	when [property] = 1 and [value] = 2 then
    N' creates a new file when max file size is reached.'
	when [property] = 1 and [value] = 3 then
    N' create a new file when max file size is reached and produce a rowset.'
	when [property] = 1 and [value] = 4 then N' shuts down the trace on an error.'
	when [property] = 1 and [value] = 5 then N' produces a rowset and shutdown on error.'
	when [property] = 1 and [value] = 6 then
    N' creates a new file when max file size is reached and shuts down on an error.'
	when [property] = 1 and [value] = 7 then
    N' creates a new file when max file size is reached, produces a rowset and shuts down on an error.'
	when [property] = 1 and [value] = 8 then N' is a Blackbox trace.'
	when [property] = 2 then N' results are in file ' + cast([value] as nvarchar(245)) + N'.trc.'
	when [property] = 3 then N' max file size is ' + cast([value] as nvarchar(5)) + N' megabytes.'
	when [property] = 4 and [value] is not null then
    N' automatically stops on ' + cast([value] as nvarchar(25)) + '.'
	when [property] = 4 and [value] is null then N' does not automatically stop on any date and time.'
	when [property] = 5 and [value] = 0 then N' is stopped.'
	when [property] = 5 and [value] = 1 then N' is running.'
	end
       ,0
	FROM :: fn_trace_getinfo(0) order by [property] desc

if (select count(*) from @traces) < 1
	begin
		select 'No traces exist.' as [Trace Information]
		goto finish
	end

if @traceid > 0
	begin
	update @traces set check1 = 1 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 or @setstatus = 1 or @setstatus = 2) goto changestatus

if @traceid < 1 select 'Trace number '  + cast([traceid] as nvarchar(3)) + [tpdesc]
                    as 'TRACE STATUS' from @traces where [property] = 5

while (select count(*) from @traces where check1 = 0) > 0
	begin
		select top 1 @traceid = traceid from @traces where check1 = 0
		select N'Trace number ' + cast(traceid as nvarchar(3)) + [tpdesc] as 'PROPERTY'
	    from @traces where traceid = @traceid order by [property] DESC
		select 'Trace number '  + cast(@traceid as nvarchar(3)) + ' is tracing event '
         + cast(a.eventid as nvarchar(3)) + ', ' + b.[event] + '.' as 'EVENT'
		  from ::fn_trace_geteventinfo(@traceid) a
		  left join @events b on a.eventid = b.eventid
		 group by a.eventid, b.[event]
		select 'Trace number '  + cast(@traceid as nvarchar(3)) + ' is tracing column '
         + cast(a.columnid as nvarchar(3)) + ', ' + b.[column] + '.' as 'COLUMN'
		  from ::fn_trace_geteventinfo(@traceid) a
		  left join @columns b on a.columnid = b.columnid
		 group by a.columnid, b.[column]
    if (select count(*) from ::fn_trace_getfilterinfo(@traceid)) > 0
		  select 'Trace number '  + cast(@traceid as nvarchar(3)) + ' filters on column '
  	 	 	   + cast(b.columnid as varchar(3)) +', ' + b.[column] + ', '
  		     + case a.comparison_operator
  					 when 0 then ' Equal '
  					 when 1 then ' Not Equal '
  					 when 2 then ' Greater Than '
  					 when 3 then ' Less Than '
  					 when 4 then ' Greater Than Or Equal '
  					 when 5 then ' Less Than Or Equal '
  					 when 6 then ' LIKE '
  					 when 7 then ' NOT LIKE '
  					 end
  				 + cast(value as varchar(50)) + ' '
		       + case a.logical_operator
		         when 0 then 'and '
  		       when 1 then 'or '
  		       end
          as 'FILTER'
  		  from ::fn_trace_getfilterinfo(@traceid) a
  		  left join @columns b on a.columnid = b.columnid
    else
      begin
        print 'Trace number '  + cast(@traceid as nvarchar(3)) + ' has no filters.'
        print ''
      end
		update @traces set check1 = 1 where traceid = @traceid
	end
goto finish
changestatus:
while (select count(*) from @traces where check1 = 0) > 0
  begin
		select top 1 @traceid = traceid from @traces where check1 = 0
		update @traces set check1 = 1 where [traceid] = @traceid
    if @setstatus = 0
     	begin
	     	exec sp_trace_setstatus @traceid, 0
       	select 'Trace number ' + cast(@traceid as varchar(3)) + ' is stopped.' as [Trace Information]
     	end
    if @setstatus = 1
     	begin
       	exec sp_trace_setstatus @traceid, 1
       	select 'Trace number ' + cast(@traceid as varchar(3)) + ' is running.' as [Trace Information]
     	end
		if @setstatus = 2
			begin
       	exec sp_trace_setstatus @traceid, 0
       	exec sp_trace_setstatus @traceid, 2
       	select 'Trace number ' + cast(@traceid as varchar(3)) + ' was stopped and deleted.' as [Trace Information]
			end
	end
finish:
set nocount off

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
Modification Type:MajorLast Reviewed:11/5/2003
Keywords:kbinfo KB283786
©2003 Microsoft Corporation. All rights reserved.