GilaMonster (5/11/2009)
Dean Jones (5/11/2009)
The value of the trace file is constantly at 0kb even though there are queries running on the system.The trace file only gets populated if I restart SQL server.
There is a buffer for the events, they don't go to file instantly. The file size does change over time.
The code: EXEC sp_trace_setstatus @traceid = 1 , @status = 2 does not delete all the trace information as SELECT * FROM ::fn_trace_getinfo(NULL) still displays it.
To remove the trace definition you need to set the status to 2 (stopped) and then set the status to 0 (close and delete)
Also, the most crucial bit is that I cannot stop the trace just by running EXEC sp_trace_setstatus @traceid = 1 , @status = 0. I need to have the flexibility of forcing the trace to stop without having to restart the SQL service.
What exactly do you mean you cannot stop it? What happens if you set the status to 0? What do you expect to happen?
Thanks for bringing this to my attention, I didnt know a buffer was set, I assume this is 128kb. I will try this out.
Also, I thought it was the other way around according to MS
To remove the trace definition you need to set the status to 0 (stopped) and then set the status to 2 (close and delete)
When it says deleted, what do you actually mean by this, If you mean deleted the trace definition, I can still see this by running
SELECT * FROM ::fn_trace_getinfo(NULL), even though proeprty 5 would be set to 0. I was expecting not to find anything from the table at all to give full confidence that its been totally deleted.
At the moment, the only way to confirm that its been indeed deleted is to restart sql service.
Also, just to confirm that the changing of the status doesnt work, i set it to 0 = to stop and 2 = to delete. and after doing this, I find that one cant delete the trace file, which means something is still actively writing/working wtih it.