In every version since SQL 2005 there has been a “background trace” enabled by default on all SQL Server installations. This was a bit of a shocking revelation to me, as I am not a DBA, since every time I have called Microsoft dozens upon dozens of times and talked to tier 3 support / product team members for SQL Server and none of them have mentioned this fact when we have been diagnosing issues in SQL. We have always had to set up a new trace.
Use the following command to validate that the default trace is enabled:
This will return something that looks like:
From MSDN we get a legend that helps us under stand the above output:
ID of the trace.
Property of the trace:
1= Trace options. For more information, see @options in sp_trace_create (Transact-SQL).
2 = File name
3 = Max size
4 = Stop time
5 = Current trace status. 0 = stopped. 1 = running.
|Information about the property of the trace specified.
The question I have heard most often since this discovery is “What does it matter? Microsoft has it turned on by default so that must mean they intended it that way. It must not cause a performance impact. Right?”
After I stopped laughing at the idea that because Microsoft turned it on by default it must be ok (see Windows 2000 and before where everything was turned on and wide open upon install) I explained that the default trace is dropped into the installed Logs directory. This cannot be changed. You can stop the default trace and create your own, but that will break some of the built in reports that rely upon it.
The key is knowing that this is there. While it may be lightweight and designed to be non-impactful, if you have anything else with a write intensive load directed at the same spindles you can expect to see some contention.
The positive of it is that now that we know it is there, it is extremely useful in troubleshooting. The default trace can be queried by using the following command to pull back the most useful data (querying * pulls back WAY too much data for my taste):