Creating an unlimited SqlServer Trace
On my app i am creating a real time trace (not sure how yet but i am!) and on the sp_trace_create function in SQlServer, i know that the @maxfilesize defaults to 5, but on my app its going to be stopped when the user wants to stop it...any ideas how this can be done?
Because i dont want to have to save the files...im not sure how the rollover works? Right now im putting it on a timer loop that queries the database with all the specified events on it with a maximum file size of 1(usually doesnt take more then about 2 seconds), merges with the old lot of data in my dgview and deletes the original file. this goes round until the user tells it to stop which will stop the timer from querying the database. Not a solid method but i guess its a start! All i need now is the find out the datatypes of the columns as when im setting my values in the filters they need to go in as the matching datatype to the column... anyone have any clue where i can get a list of the datatypes? msdn have the list but no types...
To start a trace with file rollover, instead of stopping at a maximum size, start the trace like so:
exec @rc = sp_trace_create @TraceID output, 2, N'InsertFileNameHere', @maxfilesize, NULL
where @maxfilesize will define the size reached before a new rollover file will be created.
WARNING: be very careful about performing unlimited tracing. If you fill up a production disc, it's your head not mine!
You can stop a running trace like so:
EXEC sp_trace_setstatus @ID, 0 EXEC sp_trace_setstatus @ID, 2
where @ID is the ID of the trace you want to stop.
See this post.