Jonathan Kehayias (11/10/2012)

---

John Esraelo-498130 (11/10/2012)

---

Hi Jonathan,

Before I ask a question I wanted to say that I really like your articles, especially in the events area.

Here is the question:

I have create a queue for auditing database management on alter, create, drop, etc.

everything looks fine, I can query the queue and displaying the records..

But, for some reason this queue becomes "disabled" within a minute after re-enabling and I have to manually or programmatically re-enable it.

I have created couple of other ones that work fine, such as audit login failed, and one adapted from your awesome code on AutoGrowFile..

Any advise would be grand

thx again Jonathan

Hey John,

My first guess would be that you have a poison message in the queue that is causing the activation procedure to rollback and after 5 rollbacks it will automatically disable the queue. To troubleshoot this, I would disable activation and then enable the queue and look at the next message on it to see what is wrong with it. I'd also see why the activation procedure has to rollback during processing of the message. To do this:

-- Turn activation off

ALTER QUEUE [YourQueue]

WITH ACTIVATION (

STATUS = OFF,

EXECUTE AS OWNER);

-- Enable the queue

ALTER QUEUE [YourQueue]

WITH STATUS = ON;

Then just do a standard SELECT against the queue to view it's contents.

SELECT *

FROM [YourQueue];

You can also clear the items in the queue by using RECEIVE:

DECLARE @message_body XML;

-- Get the top message from the queue

RECEIVE TOP (1)

@message_body = CAST([message_body] AS XML)

FROM [YourQueue]);

Hopefully that helps you resolve the problems.

Cheers,