Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

end conversation gets blocked by process with request_id 0? Expand / Collapse
Author
Message
Posted Tuesday, August 14, 2012 6:11 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 17, 2014 7:32 AM
Points: 412, Visits: 1,412
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?




Posting Data Etiquette - Jeff Moden
Posting Performance Based Questions - Gail Shaw
Hidden RBAR - Jeff Moden
Cross Tabs and Pivots - Jeff Moden
Catch-all queries - Gail Shaw


If you don't have time to do it right, when will you have time to do it over?
Post #1344640
Posted Thursday, August 16, 2012 1:40 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 17, 2014 7:32 AM
Points: 412, Visits: 1,412
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.



Posting Data Etiquette - Jeff Moden
Posting Performance Based Questions - Gail Shaw
Hidden RBAR - Jeff Moden
Cross Tabs and Pivots - Jeff Moden
Catch-all queries - Gail Shaw


If you don't have time to do it right, when will you have time to do it over?
Post #1345704
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse