Creating an unlimited SqlServer Trace

Question

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?

Answer 1

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.

Answer 2

Yeah ive used that one before but how can i create one that doesnt stop itself at the maxmimumfilesize ?? i want it to just run until stopped manually regardless of the max file size....

Answer 3

According to the documentation, what you want to do is not possible:

[ @maxfilesize = ] max_file_size Specifies the maximum size in megabytes (MB) a trace file can grow. max_file_size is bigint, with a default value of 5.

If this parameter is specified without the TRACE_FILE_ROLLOVER option, the trace stops recording to the file when the disk space used exceeds the amount specified by max_file_size.

I don't see why you can't just cycle through the files and load them into a table or your app. Shouldn't be that hard.

Answer 4

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...