The amount of logging that occurs in SQL Server by default is very useful. The error log is easy to look at from Management Studio, and the system_health extended event session can now be viewed through Management Studio 2012. However, examining the default trace and the ring buffers needs a little more expertise. This is where SQL CoPilot comes in.
Reports in this section are summarized below. More detail will be added soon.
The error log on a busy system can be filled with hundreds and sometimes thousands of informational messages, such as log backup completion, and messages posted by application code. This report filters all that out and displays errors and DBCC completion messages only. The last 500 messages from the current log are displayed in reverse chronological order.
The default trace was introduced in SQL Server 2005, and logs a wide range of information. The default trace reports consist of an overview, summarizing and grouping the information, with further reports available for each trace category.
There are 4 System Health reports, on for each event in the system_health extended event session.
One of these lists deadlocks, and clicking on a deadlock displays a detail report allowing the cause of the deadlock to analysed in detail.
Prior to SQL Server 2012 there was a bug in extended events that meant the event time could not be relied on for SQL instances that have not been recently restarted. As a result, the deadlock list is sorted on batch start time. When viewing the data from the other reports you should take this into account.
There are 5 reports for ring buffers, covering connectivity, exceptions, resource monitor, security and memory broker.