Service broker queues are disabling themselves, can't unearth the reason

Question

In sys.transmission_queue the only information I'm given is "One or more messages could not be delivered to the local service targeted by this dialog."

If I re-enable one of the problem queues (there are five) and leave Activation set to OFF, the queue fills up with its messages. If I then run the Activation SP, I process the messages correctly and without issue. But as soon as I turn activation ON, if there are ANY messages in the queue, the queues will disable themselves again.

I'm completely lost. Does anyone know how I can troubleshoot this?

score 14 · Accepted Answer · edited May 16 '21 at 15:11

14

The queues disable themselves as a reaction to poison message handling protection. It means that your activated procedure is rolling back, probably due to some exception. Activated procedure do not have a session to send errors to, so they send it to ERRORLOG instead. Check your errorlog, should be riddled with error messages from your activated procedures.

The easiest way to troubleshoot is to simply run the activated procedure manually, from SSMS, when the activation is OFF. Try to recreate the same execution context as activation, see Internal Activation Context. The critical part is the EXECUTE AS context which is changing a lot of behavior, especially security. So try this (assuming your queue executes under dbo):

use <dbname>;
go

execute as user = 'dbo';
go

exec <sp_my_activated_proc>;
go

revert;
go

You will likely get an error message which is probably the cause of your repeated disables.

edited May 16 '21 at 15:11

deepak raj

3,331
1
12
20

answered Mar 29 '13 at 18:17

Remus Rusanu

288,378
40
442
569

My error seems to be that user 'dbo' is not a member of the DatabaseMailUser role on msdb. But I can't seem to alter sa to be part of that role. And in the past it was not an issue. Further, my own windows login is not a member of that role but I can use database mail because I am a sysadmin. Since sa is also a sysadmin, I don't know why it can't use it... – Aushin Mar 29 '13 at 20:04
I also get four of these errors: The current transaction cannot be committed and cannot support operations that write to the log file. – Aushin Mar 29 '13 at 20:08
2

Is because your current `dbo` context does not extend to `[msdb]`. See [Extending Database Impersonation by Using EXECUTE AS](http://msdn.microsoft.com/en-us/library/ms188304(v=sql.105).aspx). you can enable [`TRUSTWORTHY`](http://technet.microsoft.com/en-us/library/ms187861.aspx) on your database or use module signing, see [Call a Procedure in another database from an Activated procedure](http://rusanu.com/2006/03/07/call-a-procedure-in-another-database-from-an-activated-procedure/) – Remus Rusanu Mar 29 '13 at 20:11
1

Also you need to fix your CATCH block. You **must** check the [`XACT_STATE`](http://msdn.microsoft.com/en-us/library/ms189797.aspx). See [Exception Handling and nested transactions](http://rusanu.com/2009/06/11/exception-handling-and-nested-transactions/) for an example. – Remus Rusanu Mar 29 '13 at 20:13

Roger Willcocks · Answer 2 · 2014-05-08T03:26:23.230

I would assume I'm getting this poison message handling.

The queue disables after the first message Happening in 2008 and 2012. But worked fine on my dev PC with 2008 r2

However, nothing shows up in the "SQL Server Logs"

If I create the queue without the activation clause and run the SQL manually, then the SP runs and returns without an error (nothing in the log again either), and the queue disables

If I manage to figure out the cause I'll update this.

It was message poisoning - silent failure after 5 rollbacks in a row.

Never did find any logs, but the cause was the NULL check I had:

WAITFOR
(
    RECEIVE TOP(1)
        @msg        = convert(xml,message_body),
        @DlgId      = conversation_handle
    FROM dbo.AuditLog_Request_Queue
), TIMEOUT 1000;

-- exit when waiting has been timed out
IF @@ROWCOUNT = 0
BEGIN
    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRANSACTION;
    END
    BREAK;
END

-- exit when waiting has been timed out
IF @msg IS NULL
BEGIN
    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRANSACTION;
    END
    BREAK;
END

Moving that to around the later EXECUTE statement stopped the problem

Service broker queues are disabling themselves, can't unearth the reason

2 Answers2