1

I have a production web site that randomly hangs. I can't recreate it on test or dev servers. We therefore ordered a trace to be collected from the MS SQL Server.

When going through the trace, I noticed that sometimes, a very long duration were logged. I also noticed that the SPID number changes every time this huge duration is recorded - see image. Is it a locked/hanging process that's unresponsive for the logged duration or how do I interpret this?

I've already been to the TechNet Library and other sites, but I can't seem to find the answer to this.

I'm afraid that I don't have the time to learn all there is about the matter or spend days finding the answer.

I'm really more in the need for a Yeah, you're screwed! or Don't worry, that duration is just the time elapsed since that SPID had to access data! or something like that.

I hope there's anyone out there able to help!

Trace File Duration

Jones
  • 85
  • 1
  • 10
  • Related to: [This](http://stackoverflow.com/questions/2132539/what-is-audit-logout-in-sql-server-profiler), [this](http://stackoverflow.com/questions/13049452/sql-server-audit-logout-creates-huge-number-of-reads), and [this](http://stackoverflow.com/questions/279401/sql-connection-pooling-and-audit-login-logout). I'm thinking, more and more, that it is an aggregate of how long that specific connection has been open, but it would be great if someone could confirm this! – Jones Jan 29 '14 at 13:59

2 Answers2

0

I think this will help you to begin: http://technet.microsoft.com/en-us/library/ms181091.aspx

There are a couple of free e-books that will help you learn how to read Profiler traces as well. Red gate has a really good one.

TTeeple
  • 2,913
  • 1
  • 13
  • 22
  • Thanks, but I've been there already. I've updated my question a bit to narrow down what I'm looking for. – Jones Jan 29 '14 at 09:13
0

Well, I can tell you that is what it is supposed to be:
http://msdn.microsoft.com/en-us/library/ms175827(v=sql.100).aspx.

It's not strange that the spid changes when there's a Logout event...that is the connection id.
It would be interesting to see if it corresponds to the beginning or end of your hang event.

Mike M
  • 1,382
  • 11
  • 23