August 14, 2012 at 6:11 am
We have one single conversation in one of our servers that can not be ended. Calling end conversation on it does not return. No messages, no log entries, just sits there. One other thing I've noticed odd is that conversations in this database are no longer cleaned at half an hour after they have been closed: they all simply remain in the closed state.
What I've done so far is to see what locks are attempted when I issue an end conversation call:
select *
from sys.dm_tran_locks
where request_session_id = 61
and request_status = 'WAIT'
This outputs:
resource_type resource_subtype resource_database_id resource_description resource_associated_entity_id resource_lock_partition request_mode request_type request_status request_reference_count request_lifetime request_session_id request_exec_context_id request_request_id request_owner_type request_owner_id request_owner_guid request_owner_lockspace_id lock_owner_address
------------------------------------------------------------ ------------------------------------------------------------ -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------- ----------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ----------------------- ---------------- ------------------ ----------------------- ------------------ ------------------------------------------------------------ -------------------- ------------------------------------ -------------------------------- ------------------
METADATA CONVERSATION_GROUP 6 $hash = 0x2630942b:0xaf7f7df4:0xcc98c2 0 0 X LOCK WAIT 1 0 61 0 0 TRANSACTION 321046475 00000000-0000-0000-0000-000000000000 0x00000001DA5ACE20:2:0 0x00000001A8CFF280
(1 row(s) affected)
So, end conversation is waiting for a single lock on some meta data that does not get granted. Looking at what process keeps the meta data locked, I find something strange:
select *
from sys.dm_tran_locks
where resource_description = '$hash = 0x2630942b:0xaf7f7df4:0xcc98c2'
output:
resource_type resource_subtype resource_database_id resource_description resource_associated_entity_id resource_lock_partition request_mode request_type request_status request_reference_count request_lifetime request_session_id request_exec_context_id request_request_id request_owner_type request_owner_id request_owner_guid request_owner_lockspace_id lock_owner_address
------------------------------------------------------------ ------------------------------------------------------------ -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------- ----------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ----------------------- ---------------- ------------------ ----------------------- ------------------ ------------------------------------------------------------ -------------------- ------------------------------------ -------------------------------- ------------------
METADATA CONVERSATION_GROUP 6 $hash = 0x2630942b:0xaf7f7df4:0xcc98c2 0 0 X LOCK GRANT 1 0 0 0 0 TRANSACTION 307015051 00000000-0000-0000-0000-000000000000 0x00000001DA5ACB10:1:1 0x00000001A8D42E00
METADATA CONVERSATION_GROUP 6 $hash = 0x2630942b:0xaf7f7df4:0xcc98c2 0 0 X LOCK WAIT 1 0 61 0 0 TRANSACTION 321046475 00000000-0000-0000-0000-000000000000 0x00000001DA5ACE20:2:0 0x00000001A8CFF280
(2 row(s) affected)
The 2nd line is obvious: this is my end conversation attempt waiting for the lock to be granted. It goes away when I kill the end conversation call and comes back when I retry it.
But what is keeping that lock at the 1st line? It has a request_session_id of 0, request_request_id of 0 and more columns that make no sense to me. It has been in this state for several days now and it doesn't go away if we stop and start any of our processes. I haven't tried restarting sql server yet, so I don't yet know if that will fix the problem. But I would like to know what is going on before I do that. This is a test system and I don't need something like this to happen in production when we go live.
The SQL server machine I'm running this on is Sql server 2005, this is because we're building an extension to an external product for which we can not yet upgrade the server:
Microsoft SQL Server 2005 - 9.00.4035.00 (X64)
Nov 24 2008 16:17:31
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
Please help me find an explanation why this conversation gets blocked and how to make sure it won't happen in production?
August 16, 2012 at 1:40 am
I have not found more information why this happened, but after a restart of the sql server the lock that was held already for almost 14 days was released and both symptoms were fixed: the closed conversations are cleaned again and the one conversation that was in 'ER' state could successfully be ended. Problem solved, but no idea what caused it.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply