How To Read Default Trace In Sql Server

The default trace was introduced in SQL 2005 as troubleshooting option and assistance to DBA’s by providing a trace log with the necessary data to diagnose problems that occur for the first time. This is a system generated server side trace which starts with SQL Server unless DBA explicitly stops it.

It is the same trace that we know from the profiler and it is a pre-configured trace from sql trace that is enabled as default.

To Read Trace file there are various ways :
SELECT TableValues.DatabaseName, TableValues.TextData, TableValues.HostName,

      TableValues.ApplicationName,TableValues.LoginName,       TableValues.StartTime, 
      ( SELECT TOP 1 f.[VALUE]                                                
        FROM    sys.FN_TRACE_GETINFO(NULL) f
        WHERE = 2
        )), DEFAULT) TableValues

select path from sys.traces where id =1
--> update path in below query
fn_trace_gettable( convert (varchar(1000),
'location of file\tracefile.trc')  , default)
where TextData is not null
order by starttime

Default trace Properties:

1.      It creates five *.TRC file.
2.      Files recycle when they arrive to 20 MB size.
        3.      To Enable Default Trace
sp_configure 'show advanced options',1
sp_configure 'default trace enabled' , 1

4. Disable Default Trace
sp_configure 'show advanced options',1
sp_configure 'default trace enabled' , 1

4.      Start \ Stop Trace : The default trace cannot be stopped or modified. Use SP_CONFIGURE to turn it off.

  1. The trace file location  select path from sys.traces where id = 1”
  2. Default trace should not be stopped as stopping it can impact couple of reports generated by sql server itself.
  3. Default trace doest create performance impact.
  4. High value of Waits  SQLTRACE_INCREMENTAL_FLUSH_SLEEP” or  “SQLTRACE_WAIT_ENTRIES” can suggest it actually be a performance issue.

In SQL Server 2012, below 34 events  are captured

Category Event
92 Database Data File Auto Grow
94 Database Data File Auto Shrink
167 Database Database Mirroring State Change
93 Database Log File Auto Grow
95 Database Log File Auto Shrink
22 Errors and Warnings ErrorLog
55 Errors and Warnings Hash Warning
79 Errors and Warnings Missing Column Statistics
80 Errors and Warnings Missing Join Predicate
69 Errors and Warnings Sort Warnings
155 Full text FT:Crawl Started
156 Full text FT:Crawl Stopped
164 Objects Object:Altered
46 Objects Object:Created
47 Objects Object:Deleted
218 Performance Plan Guide Unsuccessful
109 Security Audit Audit Add DB User Event
108 Security Audit Audit Add Login to Server Role Event
110 Security Audit Audit Add Member to DB Role Event
111 Security Audit Audit Add Role Event
104 Security Audit Audit Addlogin Event
115 Security Audit Audit Backup/Restore Event
117 Security Audit Audit Change Audit Event
152 Security Audit Audit Change Database Owner
102 Security Audit Audit Database Scope GDR Event
116 Security Audit Audit DBCC Event
106 Security Audit Audit Login Change Property Event
20 Security Audit Audit Login Failed
105 Security Audit Audit Login GDR Event
103 Security Audit Audit Schema Object GDR Event
153 Security Audit Audit Schema Object Take Ownership Event
175 Security Audit Audit Server Alter Trace Event
18 Security Audit Audit Server Starts And Stops
81 Server Server Memory Change

You might like to read : difference between SQL Profiler vs Server side Trace

No comments:

Post a Comment