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?

As you probably know, there's a trace running by default in SQL Server 2005. The directory for the trace file is the SQL Server log directory, and you can turn off and on this trace with sp_configure.

But how do we find out what events and columns are traced to this? We use a trace function and some trace catalog views:

The function fn_trace_geteventinfo returns what columns and events are captured by a configured trace. But we don't want to see the column id and event id, we want the names. So we join this to the following functions:
sys.trace_events
sys.trace_categories
sys.trace_columns

Here's the end result:

SELECT cat.name AS CategoryName, e.name AS EventName, c.name AS ColumnName
FROM fn_trace_geteventinfo(1) AS rt
 INNER JOIN sys.trace_events AS e
   ON rt.eventid = e.trace_event_id
 INNER JOIN sys.trace_columns AS c
   ON rt.columnid = c.trace_column_id
 INNER JOIN sys.trace_categories AS cat
   ON e.category_id = cat.category_id
ORDER BY CategoryName, EventName, ColumnName

And here's one with only category and event:

SELECT DISTINCT cat.name AS CategoryName, e.name AS EventName
FROM fn_trace_geteventinfo(1) AS rt
 INNER JOIN sys.trace_events AS e
   ON rt.eventid = e.trace_event_id
 INNER JOIN sys.trace_categories AS cat
   ON e.category_id = cat.category_id
ORDER BY CategoryName, EventName

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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

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

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement