April 7, 2015 at 12:22 am
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.
April 7, 2015 at 12:25 am
The instance is on 2012 sp1 cu12
April 13, 2015 at 7:22 pm
Looks like not many people saw this error before. Just logged a call with Microsoft. Hopefully the cause can be found.
April 30, 2015 at 1:14 pm
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?
April 30, 2015 at 5:09 pm
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.
April 30, 2015 at 5:26 pm
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
April 30, 2015 at 11:59 pm
Thx for the info, Ge,
I'll try that on my box 🙂
February 13, 2018 at 7:36 am
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