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 :
1.

/*************************************************/

SELECT TableValues.DatabaseName, TableValues.TextData, TableValues.HostName,

      TableValues.ApplicationName,TableValues.LoginName,       TableValues.StartTime, 
       TableValues.IsSystem
FROM sys.FN_TRACE_GETTABLE(CONVERT(VARCHAR(150),
      ( SELECT TOP 1 f.[VALUE]                                                
        FROM    sys.FN_TRACE_GETINFO(NULL) f
        WHERE   f.property = 2
        )), DEFAULT) TableValues
/*************************************************/

2.
/*************************************************/
select path from sys.traces where id =1
--> update path in below query
SELECT DB_NAME (DatabaseID) ,* FROM
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
go
reconfigure
go
sp_configure 'default trace enabled' , 1
go
reconfigure
go
/*********************************/

4. Disable Default Trace

/*********************************/
sp_configure 'show advanced options',1
go
reconfigure
go
sp_configure 'default trace enabled' , 1
go
reconfigure
go
/*********************************/

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

5. The trace file location  “select path from sys.traces where id = 1”
6. Default trace should not be stopped as stopping it can impact couple of reports generated by sql server itself.
7. Default trace doest create performance impact.
8. 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

EventID	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