The queue %d in database %d has activation enabled and contains unlocked messages but no RECEIVE has been executed for %u seconds

  • I am getting below the message in error log every 1000 seconds on one of my instances where I have a DBA database in which I use service broker to proactively monitor blocking, deadlock, etc..

    The queue %d in database %d has activation enabled and contains unlocked messages but no RECEIVE has been executed for %u seconds

    I did some investigation and even dropped the queue (based on sys.service_queue_usages and sys.services I am sure the queue has been dropped). The message is still appearing every 1000 seconds.

    Anyone has seen this before? Thanks for any help.

  • The instance is on 2012 sp1 cu12

  • Looks like not many people saw this error before. Just logged a call with Microsoft. Hopefully the cause can be found.

  • Strange...

    Just got the first one on my systems.

    The queue 2115746840 in database 4 has activation enabled and contains unlocked messages but no RECEIVE has been executed for 39426 seconds.

    Did the call @ms gave any clue?

    Grtz,

    Theo

  • Just go the same message today.

    The queue 331148225 in database 4 has activation enabled and contains unlocked messages but no RECEIVE has been executed for 2708 seconds

    Anyone have any ideas?

  • The message stopped appearing followed by our monthly patching which restarted the server hence the instance. Below is the summary supplied by Microsoft before the case was closed.

    Symptom

    ===============

    Below error is being reported in the sql server error log every 1000 seconds:

    The queue %d in database %d has activation enabled and contains unlocked messages but no RECEIVE has been executed for %u seconds

    Possible Root Cause

    ===============

    When the service broker queue was trying to be dropped, the stored procedure used to deal with the received message is still in an activated state which can cause some internal lock preventing the queue from being dropped completely. Therefore, the scoped issue was kept being reported.

    Resolution

    ===============

    After rebooting the server, the issue does not occur any longer. In order to prevent such kind of issue from happening again, below query should be run to deactivate the stored procedure before the queue is dropped:

    ALTER QUEUE <QueueName>

    WITH ACTIVATION

    (

    STATUS = OFF

    );

    Moreover, the specific error message can be suppressed by the trace flag 8460.

  • A few troubleshooting queries.

    --Run below the query you should see a queue with state "DROPPED". According to my understanding, this is not normal. Once our issue was resolved, no queue is in such a state any more.

    Select * from sys.dm_broker_queue_monitors;

    -- Run below the query you should see an unremoved activated task

    Select * from sys.dm_broker_activated_tasks;

    SELECT transmission_status, * FROM sys.transmission_queue;

    SELECT state_desc, * FROM sys.conversation_endpoints

  • Thx for the info, Ge,

    I'll try that on my box 🙂

  • I had this happen this morning for what looks like no reason (in the past it was due to database backups causing issues.  Fortunately, I have Event Notifications on our catcher, so 

    The queue 517576882 in database 9 has activation enabled and contains unlocked messages but no RECEIVE has been executed for 13293 seconds.

    https://thebakingdba.blogspot.com/2016/07/en-troubleshooting-and-fun-with.html

    TL;DR - had to restart my queues. 

    ALTER QUEUE ENAudit_SBQueue WITH ACTIVATION (STATUS = OFF);
    ALTER QUEUE ENAudit_SBQueue WITH ACTIVATION (STATUS = ON);

Viewing 9 posts - 1 through 8 (of 8 total)

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