Monitoring the Default Trace

There is a trace enabled and always running on every SQL 2005/2008 instance by default? … !

“Default trace provides troubleshooting assistance to database administrators by ensuring that they have the log data necessary to diagnose problems the first time they occur.” – SQL Server 2008 R2 Books Online

It is easy to forget the Default Trace is there because it is effortless and has no visibility in the SSMS UI. Nonetheless, the Default Trace is loaded with info that can help you. I highly recommend it. It does not produce large trace output files yet provides valuable insights – mostly about changes that have already happened  – that can be referenced when an issue arises. Gives you a much improved chance to catch a problem the first time it happens anyway – even when you aren’t looking.

What is in the Default Trace?

The Default Trace targets the kind of changes on your database server that have been common problems for folks in the past.  Not too much in the way of query execution performance data in here. The events collected of the Default Trace are revealed by the query:

select distinct evt.name
from sys.traces trc
cross apply fn_trace_geteventinfo(trc.id) info
join sys.trace_events   evt
on info.eventid = evt.trace_event_id
where trc.is_default = 1

My SQL 2008 R2 Developer Edition shows 17 Audit Events and 18 Trace events are written to this log. No where near the complete server change monitor coverage provided by SQLClue. And only a subset of the Audit events described in the SQLAudit_Application_Event_Log.sql script -the ones I found could be collected without too much output volume – nonetheless with the Default Trace a useful snapshot of the most recent change history is always available. Enough to identify application layer changes, serious index problems, auto grows, full-text, mirroring, DBCC activity and error activity all in one spot.  A mighty handy resource for any live environment:

Audit Add DB User Event
Audit Add Login to Server Role Event
Audit Add Member to DB Role Event
Audit Add Role Event
Audit Addlogin Event
Audit Backup/Restore Event
Audit Change Audit Event
Audit Change Database Owner
Audit Database Scope GDR Event
Audit DBCC Event
Audit Login Change Property Event
Audit Login Failed
Audit Login GDR Event
Audit Schema Object GDR Event
Audit Schema Object Take Ownership Event
Audit Server Alter Trace Event
Audit Server Starts And Stops
Data File Auto Grow
Data File Auto Shrink
Database Mirroring State Change
ErrorLog
FT:Crawl Aborted 
FT:Crawl Started
FT:Crawl Stopped
Hash Warning
Log File Auto Grow
Log File Auto Shrink
Missing Column Statistics
Missing Join Predicate
Object:Altered
Object:Created
Object:Deleted
Plan Guide Unsuccessful
Server Memory Change
Sort Warnings

January 2012 update: My SQL Server 2012 RC0 instance is similarly configured but with only 16 trace events. The FT:Crawl Aborted is gone. As Amit correctly points out in his great comment (Thank you Thank You Thank Amit! And Mark! Only comments to any of my posts. Much appreciated!), changes on the server from the default trace are in the Server Change History standard report, but keep in mind that that report includes only the 3 Object trace counters and excludes tempdb. There are a couple of other redundancies between this report and standard reports, but this one gives you a birds eye view of all the counters collected. That can be helpful in a crisis. And finally, there is a ReportViewer 2010 Beta compatible version of the report in the SQLClue open source project on github  – the readme.txt explains how to convert from rdl to rdlc if you drill down to the SQLClue/ReportViewerReports folder. Just reverse that process to make and rdl file out of an rdlc file that happens to include data set information. I am currently adding this report to the SQLClue UI. Hoping they don’t take too long to get the 2010 ReportViewer control out of beta… – Bill  

Any principal with the ALTER TRACE server permission (or CONTROL SERVER – the container of ALTER TRACE)  can open traces in SQL Profiler. By default this will include all members of sysadmin fixed server role on the SQL Server instance. See the  Books Online topic “Profiler, permissions“ for more details.  To verify your access try the fn_my_permissions () system function try:

select * from fn_my_permissions(NULL, 'SERVER')
where permission_name = 'ALTER TRACE';

or the inline system function HAS_PERMS_BY_NAME():

select HAS_PERMS_BY_NAME(null, null, 'ALTER TRACE');

There is almost always some interesting stuff in the Default Trace.

In Fact, if your default trace files are huge and you have not looked inside, you could be in for quite a surprise indeed. The root of your problems might have been right there in the Default Trace all along. Check out Ajmer Dhariwal’s tips for making use of Default Trace data and Amit Banerjee’s post summarization of how CSS works with Default Trace data.

Output from the Default Trace is written to the same folder as the the SQL  Server ErrorLog. If you do no know this path, it is written near the top the the ErrorLog each time SQL Server starts. The path can also be obtained by looking at the Startup Parameters in the “Advanced” tab of the interesting SQL Server’s Properties in the SQL Server Configuration Manager or as a query for the [path] column of the sys.traces catalog view, or by using the fn_trace_getinfo() system function.

select path from sys.traces where is_default = 1;

Almost always the default trace will be trace_id = 1 in the sys.traces view. That is not guaranteed. Better to use is_default, especially when automating the path fetch from the view.

To check the status of the default trace on any 2005 or better instance run: 

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'default trace enabled';

Obviously, the trace can be disabled. For most scenarios, it is remembering this resource is there that needs to change, not the fact that the Default Trace is active.

The Default Trace uses the roll-over option for trace output to limit file growth to 20MB. Also, each time the SQL Server service starts a new Default Trace file is added to the sequence. This results in the creation of a sequentially numbered series of  Default Trace output files that are then maintained by SQL Server in a manner similar to the database engine’s ErrorLog and SQLAgent logs files. If you restart the service frequently the available files may all be small. If you never restart, each file could contain 20MB of data.  Likewise, the number of logged events will vary from one scenario to the next just by the nature of the different scenarios.

The last few files are kept, the oldest file ages out once a new file is created. By default the last 5 trace output files remain available. Unlike the ErrorLog and SQLAgent Log, there is no mechanism in SSMS to modify the number of trace files to keep.   (vote for a more configurable feature at connect.microsoft.com)

I use a report that can make looking at the default trace quick and easy. The data sources are embedded queries of sys.fn_trace_gettable(). Consequently nothing else is needed to make this report run. No user database objects like stored procedures – or even a user database – are required or used by the report. I prefer to use the report from the Report Builder Preview pane. That means I need BIDS 2008 or VS 2008 installed wherever I want to see this report. The advantage is in the flexibility. More on that below.

The report is designed to provide an overview of recent entries at a glance. The row of data bars embedded in the Tablix provide a visual indication as to the number and distribution of events at each level in the Category->Class->Subclass Trace Event Class Model. Drilldown is optional but available. In the screen shot, the Security Audit Category is where most of the rows are in this sample. The red bar indicates 1224 events in the specified time interval for this category. The blue bar next to it in the the Class column indicates that all events are from a single class in that category. Hovering on a bar in the [Event Class] or [Event Subclass] columns for a quick view of the class and/or subclass depicted by that bar.

Upon expanding the Database category, it becomes obvious that not all classes have subclasses the placeholder “<empty>” is added. Also obvious is that the tempdb seems to need to grow often. Definitely worth digging into. Both a larger tempdb log size at startup and a verification that there is not something whacky with tempdb usage seem advisable.

The use of Visual Studio (VS) or Business Intelligence Developers Studio (BIDS)  as a monitoring interface is not adequately leveraged in the wild. Executing this report from Report Builder’s Preview window using BIDS (or Visual Studio 2008) in lieu of deploying to an application Report Server is well suited for administrative reporting – especially when only one – occasionally two – person(s) will ever use the report. This is not a suggestion to support or allow ad hoc report development in your production environment. Nor should this be used as premise to not test any query before use in the production environment. Nor am I advocating that you run reports on the console of production database servers. Visual Studio can crash unexpectedly and may have other undesirable side effects on the Windows host that may require a reboot. Plan accordingly. The tool only needs to be where it can see the live database servers, not directly on any production server.

A host system other than a live database server for running these administrative reports from BIDS  or – if BIDS is not an option – a Report Server deployment is advised. Using the report development environment makes it simple to change the target SQL Server Data Source or tweak the report layout as might be helpful. Using the Report Server, the report is easily modified to be runnable against one of a pre-selected list of servers else from one of a list of servers that you can fetch. Books Online has pretty good info on dynamic data sources for the Report Server method.

Within VS/BIDS, changing the Source SQL Server instance in Report Builder is quite easy.

1. 1. 1. Locate the Report Data panel
      2. expand the Data Sources folder
      3. Open the properties dialog for ‘DataSource1′
      4. Replace the SQL Instance (shown as “.\SQLEXPRESS” below) with the desired target SQL Server Instance.
      5. Hit the OK button to save the changes.

Before displaying a 1200 trace output for browse, it may be better to further limit the number of rows if possible. To limit the rows in the Tablix pop open the “Filter” expander  to provide click through filtering to show only one Database, Application, Host, and or Login. Shown below, the cursor is hovering over the “.Net SQLClient Data Provider”. The tooltip indicates 1234 events. Still too many to browse.

I can select the filter anyway, then I can look at for a database filter to go with it. However, without doing any of that I can see that the Security Audit events are one subclass and mostly in a single bar in the logarithmically scaled filter charts. Instead of browsing those 1200 rows in the Tablix, in this case it may be more productive to query the system frunction from SSMS.

Get the report’s .rdl here. Easiest to save it to the file system then add it as an existing item in a Report project to use.

Bill

evt