Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
They're typically stored in the Instance directory under MSSQL\Log dir with an extension of *.trc. Do you not see them there? –  Thomas Stringer Jun 19 '12 at 16:16
    
No, they are not there. I've searched the all of the drives on the server --- I guess now it's time to see if they're being tossed onto a network share. –  swasheck Jun 19 '12 at 16:19
    
Are you sure it's not outputting to a window or a black box? –  JNK Jun 19 '12 at 17:09
    
No, I'm not sure of that. –  swasheck Jun 19 '12 at 17:16
    
If you had sudden disk growth, and the trace was the cause, you should be able to find the file. Do a search on the drive for all *.trc files. I suspect the two are un-related, however. You still need to know why the trace is there and who started it. –  datagod Jun 19 '12 at 17:32
show 1 more comment

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
    
Thank you so much for this information. It was most helpful –  swasheck Jun 20 '12 at 14:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.