Share via

Check your SQL – default Trace

  • Article

Every installation of SQL has a default trace. The default trace was introduced in SQL 2005, It is the same trace that we know from the profiler or server but it is a preconfigured trace that is enabled as default. The default trace create five *.TRC file that are been recycle when they arrive to 20 MB size.

The default trace provides 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.

The default trace can be viewed by the profiler tool or the SQL function fn_trace_gettable.

My Default Trace

How do I know the status of the trace file or how to configure it?

I have a few things to set up:

  • The trace file
  • Stop and start the trace
  • My default trace
  • Log file PATH

let's begin.

1) SYS.CONFIGURATION system table, this system TAB contain all configuration option and their status. By running
this Query I can get the required info:

   1: SELECT * FROM  sys.configurations WHERE  configuration_id= 1568;

  2: -- Value_in_use  = the status of the option 

  3: -- is_dynamic  = if the configured value need a service restarted.

image

2) Using the SP_CONFIGURE will help to configure and change the status of the default trace

   1: sp_configure'default trace enabled',1

  2: Reconfigure

3) The system table sys.traces shows the status of the trace, path of the file, size and other information.

   1: Select * from sys.traces

The default trace file can help us investigating information regarding database events, audit events, security information error and warnings, full text events, Server Memory Change Events and much more.

In this example I’m using the a parameter to collect the last TRC file with

   1: SELECT TOP 1 value FROM sys.fn_trace_getinfo(NULL) WHERE property= 2

After identifying the correct path and the name of the recent log, we can use it here:

 

 

 

   1: Declare@LogPathsql_variant

  2: SET@logPath=(Selecttop 1 valueFROMsys.fn_trace_getinfo(NULL) WHERE property= 2)

  3: 

  4: SELECT  tg.TextData,tg.DatabaseName,tg.Error,tg.ObjectName,tg.DatabaseName,te.name,

  5:     tg.EventSubClass,tg.NTUserName,tg.NTDomainName,tg.HostName,tg.ApplicationName,tg.Spid,

  6:     tg.Duration,tg.StartTime,tg.EndTime,tg.Reads,tg.Writes,tg.CPU

  7: FROM  fn_trace_gettable(cast(@LogPathasvarchar(250)),default)AStg

  8:     Innerjoinsys.trace_eventsasteontg.EventClass=te.trace_event_id

  9: Where  tg.DatabaseName<>'tempdb'

 10: ---------------------------------------------------------------------------------------

 11: --- WHERE   te.name = 'Data File Auto Grow' 

 12: OR te.name = 'Data File Auto Shrink' = Database events

 13: ---------------------------------------------------------------------------------------

 14: --- WHERE   te.name = 'Missing Column Statistics' 

 15: OR te.name = 'Missing Join Predicate' = Errors and warnings events

 16: ---------------------------------------------------------------------------------------

 17: --- WHERE  te.name IN ('Audit Addlogin Event', 'Audit Add DB User Event', 

 18: 'Audit Add Member to DB Role Event') = Security and Audit events

 19: ---------------------------------------------------------------------------------------

 20: --- WHERE   te.name IN ('Server Memory Change') = Memory events

 21: ---------------------------------------------------------------------------------------

 22: --- WHERE  te.name IN ('Object Altered','Object Created',

 23: 'Object Deleted') = Object Events

 24: ---------------------------------------------------------------------------------------

 25: --- WHERE  te.name IN ('FT Crawl Aborted','FT Crawl Started',

 26: 'FT Crawl Stopped') = Full Text events

 27:

Bottom line

Default trace is a great and powerful way to check the health, the status and the security of the SQL Server instance. It is important to be aware of it existence and use it when necessary.

I urge you not to switch it off the default server trace, some of the build-in reports that we have on the SSMS are collecting information from the default trace file.

I heard some people saying it might cost you in performance. If you suspect the default trace to be causing you an overhead, I recommend checking the "wait type" dynamic management view (DMV) in order confirm your suspicion. (sys.dm_os_wait_stats), in this DMV look for high values in this SQLTRACE_INCREMENTAL_FLUSH_SLEEP, SQLTRACE_WAIT_ENTRIES

Each time I examine the TRC files I manage to find more and more info about my databases,

this feature has lots of benefits and I found it quite useful.

Trace file are being deprecated these days and in future version of SQL we will use X-Event known more as extended events.

I will write a post on extended events in SQL 2012 in one of the next posts

Comments

  • Anonymous
    January 01, 2003
    Excellent post. Thanks!

  • Anonymous
    September 26, 2013
    Great information and well presented. Thank you.

  • Anonymous
    February 15, 2014
    can I modify default trace max_files number ?

  • Anonymous
    February 15, 2014
    can I modify default trace max_files number ? If yes then How ?

  • Anonymous
    May 07, 2014
    Can we modify the size of the default trace files as well? If yes, How to modify? Today, we had a issue, that one of the object is dropped in Production yesterday and the trace files are overwritten by today morning. i was asked to keep the trace files for a longer period.