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 recoverabilityWe 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 recoverabilityWe 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