SQL Trace Files Location & Deletion

[Lorisj33]

Good morning,

I have a server that we setup profiler tracing on but 7,300 files with 33GB of data have been collected in the SQL Server log directory. Is there a way to set where the trace files will be stored, or will it always use SQL's log directory? How can I reduce the number of trace files that are kept? Or do I simply have to not use tracing on this server? The file dates range from 11/10 thru 11/16. It's SQL 2005 SP3 EE running on Windows 2003 SP2.

Thank you,
Lori

[Sian.Williams]

Hi Lori,

Thanks for your post.

Generally we only recommend having Trace turned on if you're looking for a specific issue on a problematic server as it can use up to 5% extra CPU usage and potentially more memory. It can, as you've experienced here, also log a large amount of data which effectively becomes useless if you're searching for a specific problem.

There isn't a way of changing the place in which the files are logged, and it can't be reduced.

For more information on collecting trace data please click the following link;

http://www.red-gate.com/supportcenter/Content.aspx?p=SQL%20Response&c=SQL_Response\help\1.3\SR_Collecting_Trace.htm

The new version - SQL Monitor - initially gathers the information collected in a trace file when Trace is turned on, however after this point it routinely overwrites it once the data file gets too big.

Also, for your information - if you still have active support and upgrades attached to your SQL Response license, you're eligible for a complimentary upgrade to the SQL Monitor which has many new features and benefits. You can check it out here - http://www.red-gate.com/products/SQL_Monitor/index.htm

If you email our sales team at sales@red-gate.com they can look into this for you.

Thanks,
_________________
--------------------------------------
Siân Williams
Red Gate Software
Tel: 0800 169 7433
sian.williams@red-gate.com
--------------------------------------