Jack Corbett (12/2/2010)

---

Great article again Grant. I just prefer to use sys.traces over fn_trace_getinfo. I think it gives an easier to decipher set of information.

Hmm, I'm going to have to play with the stop time because I thought it deleted the trace definition...

PAH-440118 (12/2/2010)

---

What is the command to delete to the trace definition?

1.Execute sp_trace_setstatus by specifying @status = 0 to stop the trace.

2.Execute sp_trace_setstatus by specifying @status = 2 to close the trace and delete its information from the server.

Grant Fritchey (12/2/2010)

---

Writing to file is much, much faster than writing to a database, so yes, it does make a difference. Also, is that database on the same server you're monitoring? That can be a problem, adding overhead where none should exist.

CavyPrincess (12/2/2010)

---

But of course it's on the same server :) Since I'm going to change process around anyway, I'll change it to create the flat files and import then into the database afterwards. I was just being lazy to save a step as it hasn't yet caused a problem. I certainly don't want to be the cause of problems, so thank-you for your response.

Cindy

ALZDBA (12/2/2010)

---

I haven't noticed it would not cleanup the trace def in systraces after the end time.

Tested in SQL2000 / SQL2005 / SQL2008R2 CU4 .... ehm .... yes SQL2008 r1 is missing

So in my instances they get cleaned up.

edited
You can find my setup script in the attachement it was to long to just post in the reply

Jack Corbett (12/2/2010)

---

I did a quick test on SQL 2008 and the definition was cleaned up when the trace stopped. My test was simpler. I used Profiler to script a trace based on the standard template and set it to run for 5 minutes. After 5 minutes the only trace found in sys.traces is the default trace.