The default trace has been around for a long time. SQL Server’s default trace was introduced in SQL 2005 to supply data for the new performance and auditing reports available through the Management Studio Object Browser. For most users, that has been the extent of their use of default trace data.
However, in this edition of the newsletter we discuss how to query and use the data from trace files While the trace is running. This is especially important for traces like the default trace that are constantly running.
As the name suggests, the default trace is on by default. It can be disabled by changing the configuration option Default Trace Enabled to zero, but that is the only control you have over it. You may not add or delete events or data columns to the default trace as you can in a normal trace.
The default trace is composed of five 20 megabyte trace files that are accessed in a round robin fashion, When the first file is full, the trace moves to the next file in the sequence. When the fifth file is full, the first file is deleted and a new file is created.
The trace files are located in the log subdirectory of SQL Server’s install directory. If you installed to the default directory, the path will look something like this:
C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\log_xxx.trc
Using fn_trace_gettable for troubleshooting
Even though you cannot change the trace to capture different data, you are able to query the trace file and specify just the data you want to see, filtering out the rest. This is made possible by the function fn_trace_gettable. This function allows you to query one or more trace files as if they were a single table. The function can query both active and inactive trace files. It can read custom traces as well as the default trace.
fn_trace_gettable takes two parameters, the path to the trace file and an integer value representing the number of rollover files to include. When you are querying the default trace, the number of files is always 5.
The trace does not store the name of the events monitored, only the event id. In order to get easily readable output you need to join fn_trace_gettable to sys.trace_events to get the name of the event. Here is a sample function call that will return the default trace data from my instance of SQL Server 2014. You will have to adjust the path and the trace file name to suit your environment:
- select e.name, t.*
- from fn_trace_gettable
- ( ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\log\log_335.trc’ , 5 ) as t
- join sys.trace_events e on e.trace_event_id = t.EventClass < li > join sys.trace_events e on e.trace_event_id = t.EventClass
This query returns all the rows in the trace with all the column information. This is not particularly useful because of the quantity of data returned. However fn_trace_gettable can be used any way that a table can be used. You can filter results with a WHERE clause, Join tables and you can limit the data returned by selecting only the columns you want to see.
Let’s look at a simple use case: Shortly after 3pm , the cpu on your sql server jumps suddenly to 100%. No scheduled jobs are running and you can’t find anything out of the ordinary going on. You suspect that a developer may have changed a stored procedure in the application database. You run this query to find out if any objects have been altered since 3pm.
- select e.name
- , t.starttime
- , t.DatabaseName
- , objectname
- , LoginName
- , t.HostName
- , t.ApplicationName
- , t.spid
- , t.eventclass
- from fn_trace_gettable( ‘C:\Program Files\Microsoft SQL Server\
- MSSQL10.MSSQLSERVER\MSSQL\Log\log_716.trc’ , 5 ) as t
- join sys.trace_events e on e.trace_event_id = t.EventClass
- where ObjectName is not null
- and t.EventClass = 164 — object altered
- and starttime > ‘2015-06-15 15:00:0.000’
- order by t.StartTime
You find that a stored procedure had been altered in the database just after 3pm. You have the developer roll back the procedure to its previous version. The CPU usage drops to a normal range.
A drawback of the default trace is that there are only 100 megabytes of the most recent data available to you. In a busy environment, 100mb of data can be generated in a very short time. If you need more history, you can periodically query the trace data into a permanent table.
Selecting Events
Knowing how to use fn_trace_gettable to extract information from a trace file gives you access to a great deal of information in the default trace for reporting, troubleshooting problems or auditing changes. You should spend some time examining what data is available and how it might be useful to you.
That is not as simple as it sounds. Events do not populate all available columns. Each event has a specific set of columns that it populates. So, to query data intelligently you must know which columns are relevant for a given event and what the data in them means.
for example, the event Audit Schema Object Access fires each time a permission is exercised on a database object. It will record when a specific user accessed the CreditCard table. It will identify the type of access, e.g. select, update, delete…and it will give you the text of the code that was executed. It will also tell you if the attempt to access the object was successful.
Finding Event Definitions
If you search a trace event name on the web you will find a Microsoft page that gives you a detailed description of both the event and the column data associated with it. Here is a link to a page describing Deadlock Graph
However each page of documentation has the details for only one event. There are over 4,000 valid combinations of events and columns that you can include in (or exclude from) a custom trace. The number of events in the default trace is fixed so it is simpler to find what you need out of what is available in that trace.
It might be faster to query the event information. The query below will give you a list of the populated columns for any event or events you specify. If you remove the WHERE clause, you will get all 4,000+ permissible combinations.
- SELECT c.name as ‘Category’, e.name as ‘Event’, col.name as ‘Column’
- FROM sys.trace_events e
- JOIN sys.trace_event_bindings b ON e.trace_event_id = b.trace_event_id
- JOIN sys.trace_columns col ON b.trace_column_id = col.trace_column_id
- join sys.trace_categories c ON e.category_id = c.category_id
- WHERE e.name in (‘Audit Schema Object Access Event’)
- ORDER BY c.name, e.name, col.name;
Custom Traces
We haven’t said much about setting up custom traces but it should be obvious that the same rules apply. Custom traces give you great flexibility to monitor events not included in the default trace.
One word of caution is to be aware of the load that another long-running trace could impose on your server. The default trace does not use the Profiler GUI and neither should your custom traces. Traces created in TSQL code put a much lighter load on the SQL Server than does a trace running in SQL Server Profiler.
Here is a link to a Microsoft page about creating traces using TSQL code.
However it s not simple to script a complex trace from scratch. I would recommend creating a trace in Profiler and then exporting it as a script template from the File menu. You can use that script as it is or you can wrap it as a stored procedure which will make it easier to change parameters, etc.