Recently I emailed a few great DBA stars with a question:
I have a question for you: do you know if there is a way to alter the properties of the default trace in SQL Server 2005 and 2008? More specifically, I am curious if we can change the number of rolled over files and their size? Maybe a trace flag? Maybe some other setting?
The design of the default trace does seem a bit like the work of an entry level developer, since the purpose of the trace is to give us performance and security picture of the present and the recent past for our SQL Server instance, however, the default trace files roll over every time the instance restarts and they are only 20 Mb each. The bottom line is, that the ‘recent past’ aspect of the default trace is limited by the volume of events (actually even the index rebuilds are logged in the default trace) and the number of instance restarts, and not by the factor time.
In other words, it would seem a bit strange to me if there was no way to make alterations to the default trace settings. After all, I know a server which retains only about 15 minutes of default trace data in all 5 files, since the server is so busy.
I have asked a few quite knowledgeable DBAs, and neither one knew a way to alter the default trace settings. This is why I am sending this email to you, with hope that maybe you would know the answer.
Let’s see what answers I get.
Update: Brent Ozar replied extremely quickly and suggested to use the Extended Events for auditing.
This sounds great, but XE is a no-go in SQL 2005.
Cindy Gross was very nice to reply as well and she suggested that I stop the default trace and create another trace with different settings to replace the default trace.
This sounds also great, but is this supported by Microsoft?
Paul Randall answered: Nope – you can’t make any changes to it at all – and agreed, that sucks.
So, that’s that. I guess now it’s clear. Håkan owes me coffee.