You are currently offline, waiting for your internet to reconnect

INF: How to Monitor SQL Server 2000 Traces

This article was previously published under Q283786
This article has been archived. It is offered "as is" and will no longer be updated.
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 GOSET ANSI_NULLS ON GOCREATE            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 onif @traceid < 0beginprint '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 finishendInsert @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] descif (select count(*) from @traces) < 1	begin		select 'No traces exist.' as [Trace Information]		goto finish	endif @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	endif (@setstatus = 0 or @setstatus = 1 or @setstatus = 2) goto changestatusif @traceid < 1 select 'Trace number '  + cast([traceid] as nvarchar(3)) + [tpdesc]                    as 'TRACE STATUS' from @traces where [property] = 5while (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	endgoto finishchangestatus: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	endfinish:set nocount offGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO				
profiler
Properties

Article ID: 283786 - Last Review: 12/05/2015 23:38:17 - Revision: 3.3

Microsoft SQL Server 2000 Standard Edition

  • kbnosurvey kbarchive kbinfo KB283786
Feedback