trc file

SQL Server 2005 / 2008 is running a default trace.

You can think of this as a replacement to the black box mode trace that could have been run in SQL Server 2000 where the last 5 MB of data is captured.

SQL Server Profiler

On the File menu, click Open, and then click Trace File.

In the Open File dialog box, select the trace data file you want to open.

Path:

C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data

trace

# View the number of currently running traces :

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

# Detail:

SELECT * FROM :: fn_trace_getinfo(default)

1    1    2
1    2    C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_31.trc
1    3    20
1    4    NULL
1    5    1
4    1    2
4    2    C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf..2014.12.11.18.09.59.trc    <--- 在用中的 file 不能直接 Delete
4    3    5
4    4    NULL
4    5    0

Result Set     Description

Traceid             Unique identifier for the trace
Property = 1     Configured trace options
Property = 2     Trace file name
Property = 3     Max file size for the *.trc file
Property = 4     Stop time for the trace session
Property = 5     Current trace status (1 = On and 0 = Off)
Value               Current value for the traceid\property combination

在典型的 SQL Server 安裝中，會啟用預設追蹤，因此它會變成 TraceID 1。
如果在安裝之後及建立其他追蹤之後啟用它，此 TraceID 可能會變成較大的數目。

To delete a trace

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.

<方式1>

# stop the trace

    EXEC sp_trace_setstatus 1, @status = 0

    # The default trace cannot be stopped or modified. Use SP_CONFIGURE to turn it off.
    
** A trace must be stopped first before it can be closed.

# close the trace and delete its information from the server.

    EXEC sp_trace_setstatus 1, @status = 2

<方式2>

EXEC master.dbo.sp_configure 'allow updates', 1;
GO
EXEC master.dbo.sp_configure 'show advanced options', 1;
GO
EXEC master.dbo.sp_configure 'default trace enabled', 0;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC master.dbo.sp_configure 'show advanced options', 0;
GO
EXEC master.dbo.sp_configure 'allow updates', 0;
GO