Sql-server – Where is this thestery trace going (SQL Server 2000)

sql serversql-server-2000

We had a sudden disk usage explosion this morning and the SQL monitor has no useful query information. I decided to check on any potential traces that could have captured something. 

SELECT * FROM :: fn_trace_getinfo(default)

gives me

traceid     property    value
-------     -------     ------
1           1           1
1           2           NULL
1           3           5
1           4           NULL
1           5           1

Property #2 should be the file name to which the trace is going but, as you can tell, it's NULL.

It's running, and just to confirm, I did this which confirms that the trace is running, has no stop condition, grows max file size of 5MB (default).

Is there a way to determine the destination of this trace, or is it a known location and my Google-FU is broken?

Best Answer

NULL means it's using the rowset provider for trace I/O. See for reference the below article:

Server-Side Tracing and Collection (section "Querying Server-Side Trace Metadata")

This query returns the trace status, which will be 1 (started) or 0 (stopped); the server-side path to the trace file (or NULL if the trace is using the rowset provider); the maximum file size (or again, NULL in the case of the rowset provider); information about how many buffers of what size are in use for processing the I/O; the number of events captured; and the number of dropped events (in this case, NULL if your trace is using the file provider).

Note For readers migrating from SQL Server 2000, note that the sys.traces view replaces the older fn_trace_getinfo function. This older function returns only a small subset of the data returned by the sys.traces view, so it’s definitely better to use the view going forward.

For details on rowset provider see for example:

SQL Trace Architecture and Terminology

The rowset provider, on the other hand, is not designed to make any data loss guarantees. If data is not being consumed quickly enough and its internal buffers fill, it waits up to 20 seconds before it begins jettisoning events in order to free buffers to get things moving. The SQL Server Profiler client tool will send a special error message if events are getting dropped, but you can also find out if you’re headed in that direction by monitoring SQL Server’s TRACEWRITE wait type, which is incremented as threads are waiting for buffers to free up.

Related Question