THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Whats in the default trace?

This blog has moved! You can find this content at the following new location:

http://sqlblog.karaszi.com/whats-in-the-default-trace/

Published Monday, March 12, 2007 12:23 PM by TiborKaraszi

Comments

 

Linchi Shea said:

Here's another way to reveal what a SQL2005 trace does (based on the code from a KB article):

CREATE proc dbo.spTrace  

   @traceid int = 0,

   @setstatus int = -1

as

   declare @events table (eventid int, [event] varchar(200))

   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 spTrace stored procedure:'

   print ' '

   print 'EXEC dbo.spTrace [@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 'EXEC dbo.spTrace 0,0'

   print ' '

   print 'Example 2.  Start trace number 1.'

   print 'EXEC dbo.spTrace 1,1'

   print ' '

   print 'Example 3.  Stop and delete all traces.'

   print 'EXEC dbo.spTrace 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')

   -- The following event numbers 119 through 202 are added for SQL2005

   Insert @events values (119,'OLEDB Call Event')

   Insert @events values (120,'OLEDB QueryInterface Event')

   Insert @events values (121,'OLEDB DataRead Event')

   Insert @events values (122,'Showplan XML')

   Insert @events values (123,'SQL:FullTextQuery')

   Insert @events values (124,'Broker:Conversation')

   Insert @events values (125,'Deprecation Announcement')

   Insert @events values (126,'Deprecation Final Support')

   Insert @events values (127,'Exchange Spill Event')

   Insert @events values (128,'Audit Database Management Event')

   Insert @events values (129,'Audit Database Object Management Event')

   Insert @events values (130,'Audit Database Principal Management Event')

   Insert @events values (131,'Audit Schema Object Management Event')

   Insert @events values (132,'Audit Server Principal Impersonation Event')

   Insert @events values (133,'Audit Database Principal Impersonation Event')

   Insert @events values (134,'Audit Server Object Take Ownership Event')

   Insert @events values (135,'Audit Database Object Take Ownership Event')

   Insert @events values (136,'Broker:Conversation Group')

   Insert @events values (137,'Blocked Process Report')

   Insert @events values (138,'Broker:Connection')

   Insert @events values (139,'Broker:Forwarded Message Sent')

   Insert @events values (140,'Broker:Forwarded Message Dropped')

   Insert @events values (141,'Broker:Message Classified')

   Insert @events values (142,'Broker:Transmission')

   Insert @events values (143,'Broker:Queue Disabled')

   Insert @events values (144,'Reserved')

   Insert @events values (145,'Reserved')

   Insert @events values (146,'Showplan XML Statistics Profile')

   Insert @events values (148,'Deadlock Graph')

   Insert @events values (149,'Broker:Remote Message Acknowledgement')

   Insert @events values (150,'Trace File Closed')

   Insert @events values (151,'Reserved')

   Insert @events values (152,'Audit Change Database Owner')

   Insert @events values (153,'Audit Schema Object Take Ownership Event')

   Insert @events values (154,'Reserved')

   Insert @events values (155,'FT:Crawl Started')

   Insert @events values (156,'FT:Crawl Stopped')

   Insert @events values (157,'FT:Crawl Aborted')

   Insert @events values (158,'Audit Broker Conversation')

   Insert @events values (159,'Audit Broker Login')

   Insert @events values (160,'Broker:Message Undeliverable')

   Insert @events values (161,'Broker:Corrupted Message')

   Insert @events values (162,'User Error Message')

   Insert @events values (163,'Broker:Activation')

   Insert @events values (164,'Object:Altered')

   Insert @events values (165,'Performance Statistics')

   Insert @events values (166,'SQL:StmtRecompile')

   Insert @events values (167,'Database Mirroring State Change')

   Insert @events values (168,'Showplan XML For Query Compile')

   Insert @events values (169,'Showplan All For Query Compile')

   Insert @events values (170,'Audit Server Scope GDR Event')

   Insert @events values (171,'Audit SErver Object GDR Event')

   Insert @events values (172,'Audit Database Object GDR Event')

   Insert @events values (173,'Audit Server Operation Event')

   Insert @events values (175,'Audit Server Alter Trace Event')

   Insert @events values (176,'Audit Server Object Management Event')

   Insert @events values (177,'Audit Server Principal Management Event')

   Insert @events values (178,'Audit Database Operation Event')

   Insert @events values (180,'Audit Database Object Access Event')

   Insert @events values (181,'TM: Begin Tran Starting')

   Insert @events values (182,'TM: Begin Tran Completed')

   Insert @events values (183,'TM: Promote Tran Starting')

   Insert @events values (184,'TM: Promote Tran Completed')

   Insert @events values (185,'TM: Commit Tran Starting')

   Insert @events values (186,'TM: Commit Tran Completed')

   Insert @events values (187,'TM: Rollback Tran Starting')

   Insert @events values (188,'TM: Rollback Tran Completed')

   Insert @events values (189,'Lock:Timeout(timeout > 0)')

   Insert @events values (190,'Progress Report: Online Index Operation')

   Insert @events values (191,'TM: Save Tran starting')

   Insert @events values (192,'TM: Save Tran completed')

   Insert @events values (193,'Background Job Error')

   Insert @events values (194,'OLEDB Provider Information')

   Insert @events values (195,'Mount Tape')

   Insert @events values (196,'Assembly Load')

   Insert @events values (197,'Reserved')

   Insert @events values (198,'XQuery Statis Type')

   Insert @events values (199,'QN: subscription')

   Insert @events values (200,'QN: parameter table')

   Insert @events values (201,'QN: template')

   Insert @events values (202,'QN: dynamics')

   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 @columns values (45,'LinkedServerName')

   insert @columns values (46,'ProviderName')

   insert @columns values (47,'MethodName')

   insert @columns values (48,'RowCounts')

   insert @columns values (49,'RequestID')

   insert @columns values (50,'XactSequence')

   insert @columns values (51,'EventSequence')

   insert @columns values (52,'BigintData1')

   insert @columns values (53,'BigintData2')

   insert @columns values (54,'GUID')

   insert @columns values (55,'IntegerData2')

   insert @columns values (56,'ObjectID2')

   insert @columns values (57,'Type')

   insert @columns values (58,'OwnerID')

   insert @columns values (59,'ParentName')

   insert @columns values (60,'IsSystem')

   insert @columns values (61,'Offset')

   insert @columns values (62,'SourceDatabaseID')

   insert @columns values (63,'SqlHandle')

   insert @columns values (64,'SessionLoginName')

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

       when [property] = 3 then N' max file size is ' + cast([value] as nvarchar(10)) + N' megabytes.'   -- chaned from nvarchar(5)

       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

March 23, 2007 2:21 PM
New Comments to this post are disabled
Privacy Statement