Saturday, June 11, 2011

SQL Server Trace Files Consuming Lot of Space

Recently we faced a situation in which Hard Drive of one of our server machines was getting filled up. Investigation resulted in the discovery that a large number of trace files were being created
Initial study developed an opinion that it was due to default trace, however a formal study resulted in the finding which have been listed in the form of following Q/A.
Note: Information Applies to SQL Server 2005 and later versions

WHY DEFAULT TRACING IS DONE?
Supply auditing and performance data to reports available through the Management Studio Object Browser. E.g. Schema change history report [1]
HOW CRITICAL IT IS?
It depends upon the scenario. The best approach is to see the information being recorded in the trace to determine whether the information is suitable for you or not [2]
HOW RESOURCE GREEDY IT IS?
“The default trace is composed of five 20 megabyte trace files that are accessed in a round robin fashion, When the first file is full, the trace moves to the next file in the sequence. When the fifth file is full, the first file is deleted and a new file is created” [1]
In case a lot of space is consumed and a lot of files are being created then there is a possibility that other traces are running as well.
HOW TO FIND OUT WHAT TRACES ARE RUNNING?
The following command can be used [3]
SELECT * FROM fn_trace_getinfo(default);
GO
HOW TO STOP A TRACE?
Information regarding stopping a trace can be found in [2],[4],[5].



REFERENCES
[1] Audit SQL Server with the Default Trace, July 2007, Online: http://www.sqlconsulting.com/news1007.htm, Accessed: 07 June 2011
[2] Kadlec, J. Default Trace in SQL Server 2005, MSSQL Tips, November 2006, Online: http://www.mssqltips.com/tip.asp?tip=1111, Accessed: 07 June 2011
[3] fn_trace_getinfo (Transact-SQL), MSDN, http://msdn.microsoft.com/en-us/library/ms173875.aspx Accessed: 07 June 2011
[4] sp_trace_setstatus (Transact-SQL), MSDN, http://msdn.microsoft.com/en-us/library/ms176034.aspx, Accessed: 07 June 2011