Home / server / Questions / 165085
In Process
Mark
Asked: 2010-07-29 16:37:23 +0800 CST

Deletion of SQL Profiler Trace files (.trc)

  • 772

We've noticed a lot of .trc files in our SQL data folder (\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data) on our server. The date range for these files spans over one day and the total file size of all files together is about 21 gigs. I'd like to free up this space but I'm not sure if I can just delete the files manually through Windows Explorer or if I need to do anything in SQL, like run a command or script. Any ideas?

sql-server trace

3 Answers

  1. The .trc files are safe to delete.

    .trc files generated by SQL Server in process of saving events to a physical file without using the Profiler client tool. Server-side tracing is enabled and controlled by using SQL Server system-supplied stored procedures and functions. With these system-supplied processes, you can identify what to trace, when to start and stop tracing, what traces are running, and view trace information stored in the trace file.

    View the number of currently running traces :

    SELECT count(*) FROM :: fn_trace_getinfo(default) WHERE property = 5 and value = 1

    More detail about the running traces:

    SELECT * FROM :: fn_trace_getinfo(default)

    You can terminate a trace with the 'sp_trace_setstatus' stored procedure using the traceid:

    EXEC sp_trace_setstatus 1, @status = 0
EXEC sp_trace_setstatus 1, @status = 2

    Setting the status to 0 stops the trace Setting the status to 2 closes the trace and deletes its definition from the server

    Good Luck!

    • 4

  2. If the trace files are no longer in use, then they should be fine to delete. I'd say someone was doing some troubleshooting of an application and forgot to delete the trace files when they were done.

    A trace file would only be in use if the trace is still running. If the files are more than a day old then it's a safe bet that the trace is no longer running. If the trace IS running the file will be locked and you won't be able to delete it anyway.

    • 1

  3. are the traces still rolling over? what are the files name like? make sure it's not something like an audit trace. Filenames are typically audittrace[datetime].trc

    It could also be the default trace (2005+), although its unusual that it would be that large in size and these are typically found in the \MSSQL.1\MSSQL\LOG

    Just to be sure, have a look at sys.traces to make sure the trace isn't still running , or stopped but still defined, or it is in fact the default trace. You should only be seeing your default trace in there if its turned on, with an id of 1.

    Either way, as long as you don't need the traces their safe to delete. You just want to be sure they dont come back!

    • 0