Help Understanding SLEEPING Status | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Help Understanding SLEEPING Status

I’m trying to write a script to inform me whenever a long-running transaction is detected. I’m only displaying results if open_tran > 0 and spid > 50. I was originally excluding records where status = SLEEPING, but I’m not sure that is correct. As I now understand it (or not) sleeping means the SPID is awaiting a command from the application or is awaiting access to a locked resource. That being said shouldn’t I be including records with a status of sleeping since the transaction is still active based upon open_tran > 0? Also, what does it mean if open_tran > 0, status = sleeping and no locks are held? Is this an indication of a poorly written application due to an incomplete transaction? Thanks, Dave
Yes, open_tran > 0 together with status = sleeping should be included. The reason you don’t see any locks could be due to the transaction included selects only. If any update, delete or insert statmens where included in the transaction those locks would still be held as I understand it. I’ve mostly had issues with this in web environments where people have long running "report like" queries that time out. It’s usually fixible in the application by either removing transaction handling where it’s not needed, increasing script timout or even better include error handling. This is a good KB on the topic:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q224453
]]>