Head blocker - Sleeping process with select command on empty table

  • Hi,
    I have problem with troubleshoot blocking issue on SQL 2008 (10.0.5500), witch happens 5 to 10 times per day. 
    For example, simple query 'select name from table' on empty table with status sleeping,  with wait info: LCK_M_ caused blocking other processes.
    SQL Server engine issue?
    Any ideas?

  • How do you know that process is the head blocker?  If it's waiting for a lock then something else must be blocking it.  Do you have sp_whoisactive?

    John

  • No, it won't be an engine bug.
    Uncommitted transaction most likely. Exclusive locks are held until the transaction commits. Look at the open transactions, and make sure that you commit/rollback any transactions that are started.

    Edit: And yes, if it is waiting for locks, it's not the head blocker. The head blocker will be running, runnable or waiting for something that's not a lock.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • You are right, there is no wait on that session, wrong description.
    Other sessions are blocked by - LCK_M_S
    Query : (@P1 int)SELECT COUNT('A') FROM [dbo].[WTR9] T0 WHERE T0.[DocEntry] = (@P1)
    Table is empty.
    dbcc opentran - No active open transactions.
    sp_whoisactive result:

  • That session has an open transaction. The column 'open tran count' shows 1, so open transaction, holding locks until commit/rollback. That's why it's blocking other sessions.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply