How do I determine, after it stopped, if a trace caused high CPU utilization to the point we could not connect?
I have a server that was brought to its knees yesterday, and the time frame matches perfectly with a trace that I can see someone else starting and stopping in the SQL Logs. However, I don't know what that trace's output was or where it was written. My vague understanding is that trace IDs get reused (i.e., yesterday's Trace ID 2 could be totally different from today's Trace ID 2), so I cannot just query fn_trace_geteventinfo() to determine what yesterday's trace 2 was looking at. So: Am I correct about not being able to easily look back at a stopped trace without the actual trace file? And does anyone know any technical workarounds? I want to exhaust those before applying soft skills.
Yes, a trace ID could be reused. Also, traces can be deleted and added by anyone with permissions, and if someone was using something like Profiler you'll only see the trace temporarily (while Profiler is running) as it's a good example of this. You do need the actual trace file.