What is SQL Server's default trace? Is it enabled?

If it's not, it really should be.  This trace is enabled by default w/your SQL Server installation;  it is very light weight, and collects details about SQL Server activity that are useful when assessing server events, or troubleshooting problems.  It is very useful for many things, but in this post I will just provide basic queries to see if it's enabled/running, what events it captures, and a couple examples of reading the default trace output file.

  -- is the default trace configured / enabled  

  SELECT* FROM sys.configurations WHERE configuration_id = 1568

No need for any screenshots.  The above just tells us if the default trace is enabled, and this one will tell us how it is configured:    SELECT * FROM sys.traces

My screenshot is cut off, but these are the important points ---  

	status	0 = stopped  /  1 = running
	path	where the file is at
	max_size	maximum trace file size in MB
	max_files	max number of rollover files
		when file reaches 20MB, another is created, up to 5 files
		then they are cycled through / rolled over / overwritten in sequence

That max_files setting is important.  Say you had a problem a few minutes ago and you need to see what the trace captured.  Well, if your system is busy, those trace files can be overwritten pretty quickly... so you shouldn't waste any time getting in there.  

Ok.  How do you read the file?  This is a simple SELECT of all data from the trace file (be sure to upate your file path/name): 

  SELECT *

  FROM fn_trace_gettable('C:\Program Files\Microsoft SQL Server\...log_175.trc',DEFAULT);

A more useful query might be to go in specifically, and see what was happening when the problem occured.  This query returns an aggregate of all events by database, application and login:

   -- see how much is going on by db/app/login

   SELECT

      te.name [EventName],

      t.DatabaseName,

      t.ApplicationName,

      t.LoginName,        

      COUNT(*) [Count]

   FROM   

  dbo.fn_trace_gettable('C:\Program Files\Microsoft SQL Server\...log_175.trc',0) t

        JOIN sys.trace_events te ON t.EventClass = te.trace_event_id

   GROUP BY

      te.name,

      t.DatabaseName,

      t.ApplicationName,

      t.LoginName;

Sample output... from my very quiet instance:

Note those missing column statistics!!!  That shows you that the default trace can also be used to observe performance conditions such as missing column stats, hash warnings, etc.

On that note, you can use this query to return the events are captured by the trace, so you know what you can actually collect from the file:

       -- what events are captured in the default trace

       DECLARE @id INT

       SELECT @id=id

       FROM sys.traces

       WHERE is_default = 1

       SELECT DISTINCT EventID, [name] [Event]

       FROM fn_trace_geteventinfo(@id) evi JOIN sys.trace_events ev

         ON evi.eventid = ev.trace_event_id 

Here is a snapshot of the results: 

As I said, there are many different uses of the defaut trace data capture, but this should get your started.  I will post more later.

More details:  sys.traces and sys.fn_trace_gettable

Oh yes!  One more thing.  If the SQL Server default trace is not enabled, this is how you enable it.  The change is immediate.  No service restart is necessary

       EXEC dbo.sp_configure 'show advanced options', 1;

       RECONFIGURE WITH OVERRIDE;

       EXEC dbo.sp_configure 'default trace enabled', 1;

       RECONFIGURE WITH OVERRIDE;

Happy tracing.  😏