I'm running a trace based on the SP_Counts template which will help me track all active sprocs in a given database. My intent was to run the trace 24/7 over the course of several weeks to capture as many sprocs as possible.
Here's my setup:
-- Server 2: Profiler is running the trace and collecting sproc usage data from Server 1.
-- Server 2: A maintenance database houses the table into which the trace results are saved.
-- Server 2: Hourly, a job is run to aggregate the sproc data from the trace table into a different table; the profiler table is then truncated.
What I didn't plan or ask for - and the point of this question - is the trace being reset at around 2:30a every morning. When I look at the default trace from Server 1, I see the sproc sp_trace_setstatus was called with the TraceID 2 (there are only
2 traces running on my production server; 1 is the default; 2 is my sproc collection trace) and a status of '2' (close the trace and delete trace info) at the time of the reset.
I've looked over all the jobs I've created between the two servers and there's nothing on either of them running at the time of the reset. Since I'm dumping the results to a table (and did not limit the number of rows) I don't think the 'max size'
property of the trace would matte
View Complete Post