Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> SQL Server >> SQL Service Broker

The dialog has exceeded the specified LIFETIME

Hi,

I'm trying to get the HelloWorld_CLR project that comes with the SQL 2005 samples download to work, which it seems to. However, after I run the application a few times, an exception indicating "the service queue ClientQueue is disabled." After running the following query I notice the body of the error message is consistently "The dialog has exceeded the specified LIFETIME."

select service_nameas [SERVICE],

message_type_nameas [TYPE],

CAST(message_bodyasnvarchar(max))as BODY

from ClientQueue

I believe that the problem is a poison message causing a RECEIVE statement to get rolled back 5 times, disabling the queue. My question is: How do I troublshoot the issue from this point?

Thanks,

Chris

[1564 byte] By [ChrisB7] at [2007-12-19]
«« Sending large block of data through CGI interface failed
»» dropdown item ID
# 1

What happens is that once the target queue gets disabled, any new messages sent are pending in sys.transmission_queue, waiting for the target queue to become enabled again. If this waiting exceeds the original dialog lifetime, specified in the BEGIN DIALOG statement, then the dialog will be errored with the error message you're seeing.

You need to figure out why is the queue being disabled in the first place. You are right that most likely culprit is the poison message support being activated, so you need to figure out why are you rolling back the RECEIVEs. Most likely an exception is being triggered, and the transaction is rolled back. A good starting point to investigate this is to use the SQL Profiler, profile T-SQL events (e.g stmt completed) and Exceptions events.

HTH,
~ Remus

RemusRusanu at 2007-9-9 > top of Msdn Tech,SQL Server,SQL Service Broker...
SQL Server
SQL Service Broker

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified