Microsoft KB Archive/283786

= INF: How to Monitor SQL Server 2000 Traces =

Article ID: 283786

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



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.

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

Additional query words: profiler

Keywords: kbinfo KB283786

-

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

© Microsoft Corporation. All rights reserved.