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/C ... L_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,