How to read Trace Information

m.cecili

I tried to activate Trace to have more information about deadlocks.
I need an explanation how to read extra information.
I saw that in Trace tabsheet now i can click every single process row and read other information about the executed sql.
My problem was that the basic information about deadlock, more exatly the line of code that generated dead lock, was wrong.
We use a lot of stored procedure very long that call other stored procedure and is difficult to understand which line of code causes the problem.
I hoped that enabling trace, it could help me to understand but i don't know how to read all logged information. Can you help me?

m.cecili

When i enable trace the informations are not immediatly available. I saw an information tip which say: NO SQL statements to display. No statement captured for the selected process...
After how many time trace information are displayed? Is there a fixed rule?

Chris Spencer

On activating trace in SQL Monitor we immediately set trace running on the relevant monitored server. When an alert is raised that supports trace (going from memory this would be Deadlocks, Long-running Query, Job Failed, Blocked Process and Error Log entries), we read the trace files and transfer data for the relevent process and time period into our data repository. The recently raised alert should display this trace data although not always straight away. If there's a lot of trace data in the time period that the alert was raised it takes a while to transfer so there could potentially be a couple of minutes lag before the trace data appears. I believe that you'd need to reopen the alert to get the trace to appear.

So do you see the call to the stored procedure but not the code in the stored procedure itself?

Regards
Chris