Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

What is this default trace you speak of?

I frequently see the default trace mentioned in answers to forum questions, blog posts, articles, even training sessions. My knowledge of it after 15+ years with SQL Server unfortunately minimal. I know that it is a trace that is created by SQL automatically (hence the default) and that it is only so big and cycles the space. Not much right?

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

DefaultTrace

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

Event Id
18 Audit Server Starts and Stops
20 Audit Login Failed
22 ErrorLog
46 Object:Created
47 Object:Deleted
55 Hash Warning

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.


Filed under: Microsoft SQL Server, SQLServerPedia Syndication, System Functions and Stored Procedures Tagged: default trace, microsoft sql server, system functions

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...