So take some time this month and review what information is available (for example the deadlock graphs), run some tests, and read the data.

You get to see the old value as well as the new, who did it, and when. The big downside is the fact that the default trace is only so big so the more active your instance is the less time you have before the information goes away. Because of this, you might consider creating a daily job to pull the information into a logging table. You won’t be able to use the report but at least you’ll have the data.

Per BOL you get the following information:

• Errors with a severity of >= 20.
• Memory related errors (Errors 17803, 701, 802, 8645, 8651, 8657 and 8902).
• Non-yielding scheduler problems (Error 17883).
• Deadlocks.
• Sessions that have waited on locks for > 30 seconds.
• Sessions waiting for a long time on preemptive waits (waits on external API calls).
• CLR allocation & virtual allocation failures.
• Ring_buffer events for the memory broker, scheduler monitor, memory node OOM, security, and connectivity.
• System component results from sp_server_diagnostics.
• Instance health collected by scheduler_monitor_system_health_ring_buffer_recorded.
• CLR Allocation failures.
• Connectivity errors using connectivity_ring_buffer_recorded.
• Security errors using security_error_ring_buffer_recorded.

 
Of that the most commonly used (at least in my experience) are the deadlocks and errors, although sessions waiting on locks could be very interesting information too. Note: this isn’t the same data that you see in the default trace.

Ok, so we have the data being collected but where is it being stored? If you look in SSMS you’ll see it has two targets.

And when I scripted mine out I was able to see that the file target is called system_health.xel and a quick directory search (on system_health*.xel) found that it’s not an easy file to locate. Under the Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\ directory there are date/time directories. Under those are various files including the system_health[big long number].xel

Ok, so now that we know what and where the system_health extended event session is/goes how do we see the data?

Well, I recommend going here. There is a ton of information in there. In general though:

You can open one or more of the files using SSMS:

View target data:

When you view the ring_buffer data it comes out as XML which may or may not be easier to work with depending on your needs.

Watch Live Data

This is going to be new data only, no history.

sys.fn_xe_file_target_read_file

SELECT * FROM sys.fn_xe_file_target_read_file ('system_health*.xel',null,null,null)

Fair warning on this one, again, the event_data is in XML. You have to use XQuery to work with it. The big benefit here is that you can write queries to parse your data and only look at specific things (deadlocks for example).

So let’s try to learn a bit more. First BOL. My search of BOL came up with “default trace enabled option”

The default trace functionality provides a rich, persistent log of activity and changes primarily related to the configuration options.

It also appears that I’m a bit late to the party.

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Extended Events instead.

If you take a look back you will see the message first showed up in SQL 2012. It’s still around though so let’s continue a bit deeper.

You can read from the default trace using the fn_trace_gettable system table function.

SELECT * 
FROM fn_trace_gettable
('C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\LOG\log.trc', default);
GO

This is the query found in the link above and it assumes the default location of a default instance of 2014. You can confirm the actual location by using the sys.fn_trace_getinfo system table function. You can also assume that the traceid is 1 for the default trace although this isn’t necessarily the case.

SELECT *
FROM sys.fn_trace_getinfo(1); -- 0 or default to get the list of all traces
GO

Another (better) option is to look at the sys.traces system view. This has even more information and is just as easy to use.

SELECT *
FROM sys.traces
GO

I should note that I cut off the list of columns displayed. I’m only displaying those columns that I’m interested in for this post. First if you look at the is_default column you will see that this is in fact the default trace. Next if you look you will see that is_rollover is set to 1. This means that the trace will fill the existing file up to the max_size column (in this case 20mb) and then begin a new file. If it reaches the max_files (in this case 5) then it deletes the first file before starting the next. So the important thing here is that the default trace is a rolling 80-100mb. It is obviously meant to just tell us what has happend on the instance recently.

But what exactly is it going to tell us? Once we are sure what the traceid is we can see what events are used in the default trace using sys.fn_trace_geteventinfo. I’m not worried about which columns are being pulled so I’m going to do a SELECT DISTINCT eventid.

SELECT DISTINCT eventid
FROM sys.fn_trace_geteventinfo(1);
GO

This gives us a list of events which we can resolve using the information in the BOL entry for sys.sp_trace_setevent.

A couple of examples in the 2012 default trace include

So now that we know the technical specs (or at least some of them), the question becomes “Why is there a default trace at all?” If you look you will notice that after an instance restart a new file is created and the first (if more than five is deleted). This leaves the previous 4 files intact. So if the server fails for some reason, if you are reasonably quick, you can take a look at some of what was going on before the crash. This can be very helpful during a postmortem.