Create Unlimited SqlServer Trace
In my application I am creating a realtime trace (not sure how else but me!) And in the sp_trace_create function in SQlServer, I know the default @maxfilesize is 5, but in my application it stops when the user wants to stop it ... any ideas how this can be done?
Because I don't want to save files ... im not sure how rollover works? At the moment I am putting it in a timer loop that queries the database with all specified events on it with a maximum file size of 1 (it usually takes no more than about 2 seconds), merges with the old data set in my dgview, and deletes the original file. this continues until the user tells him to stop, which will stop the timer from querying the database. Not a solid method, but I think this is a start! All I need now is to figure out the datatypes of the columns, as if I have set my values in the filters they need to include as the corresponding datatype in the column ... anyone have any hint where I can get the list of types data? msdn has a list but no types ...
source to share
To start the trace while scrolling the file, instead of stopping at the maximum size, start the trace as follows:
exec @rc = sp_trace_create @TraceID output, 2, N'InsertFileNameHere', @maxfilesize, NULL
where @maxfilesize will define the size reached before creating the new rollover file.
WARNING : Be very careful when doing unrestricted tracing. If you fill up a production disk, this is not your head!
You can stop tracing like this:
EXEC sp_trace_setstatus @ID, 0
EXEC sp_trace_setstatus @ID, 2
where @ID is the identifier of the track you want to stop.
See this post .
source to share
According to the documentation, what you want to do is not possible:
[@maxfilesize =] max_file_size Specifies the maximum size in megabytes (MB) that the trace file can grow. max_file_size is bigint with a default of 5.
If this parameter is specified without the TRACE_FILE_ROLLOVER option, tracing stops writing to the file when the used disk space exceeds the amount specified by max_file_size.
I don't understand why you can't just iterate over the files and load them into a spreadsheet or your application. Shouldn't be that hard.
source to share