Microsoft SQL Server Default Trace and its Benefits
Default Trace was introduced starting with SQL Server 2005, and is one of the very useful feature in some situations. It was not very popular or was not much talked feature initially, but as years progressed it came in to light during various situations where DBAs and management were desperate of finding certain information on who could have performed a certain critical operation against the SQL Server Database.
Default Trace is almost similar to server side trace, but this comes by default meaning this is enabled by default with the installation of the SQL Server instance and since then will start collecting some important pieces of information. As per BOL “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.”
The default Trace becomes very helpful in various cases while troubleshooting because it collects various events which are worth looking into when there is some unexpected event that occurs like a particular object was dropped, which should have not been or in cases were auto-growth or shrink operation caused some performance problems during business hours and we need to find who performed that task. It would be interesting and important to know what all events are captured by the default trace and we can find this information easily by browsing to the log folder where all SQL errorlogs are present and in same location the default trace files are also located. Open one of the default trace file and go to its properties and we can find all the events captured by the default trace.
If you are looking for options to add additional events to be captured by the default trace, then it is not possible as it is not supported, so you will need to setup a different server side trace or look for other alternatives. If you have implemented other alternatives and the default trace is not required any more, it can be disabled too using sp_configure. Default trace is very light waited, so you can just leave it running as you never know when it may become important and only source where you can get the information you are looking for.
Also from SQL Server Management Studio (SSMS) we can see various reports like schema change history, and few other information. We can open the default trace files using the SQL Server Profiler utility or we can read the contents of the default trace using T-SQL queries as well and it will be helpful in querying for a particular event or at a particular date and time. SQL Server maintains 5 default trace files in the log folder and overwrites once all the 5 files are used. Each file is can be a maximum of 20 MB after which it will create a new trace file and once it reaches 5 trace files, it will start removing the oldest trace file, thus ensuring there are 5 trace files at any given point in time. If the server has lot of activity then it may be possible that the old data which you may be looking for may already been lost.
This is applicable on below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.