philcart (12/23/2009)

---

Great article Jonathan, looking forward to more. Especially interested in extended events.

vinay.kumar (7/6/2012)

---

HI,

Thanks For the Post.

I tried the sample Event Notification given in the blog. And it looks all fine.

For the each Event(ERRORLOG) it is using one Conversation Group for all the raised errors. SO i manually ended the conversation and then i raised a custom exception. This time no messaes sent to any Queue.

Please let me know your thought

Thanks,
Vinay K

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

 -- Turn activation off
ALTER QUEUE [YourQueue]
WITH ACTIVATION (
STATUS = OFF,
EXECUTE AS OWNER);

-- Enable the queue
ALTER QUEUE [YourQueue] 
WITH STATUS = ON;

SELECT *
FROM [YourQueue];

DECLARE @message_body XML;

-- Get the top message from the queue
RECEIVE TOP (1) 
@message_body = CAST([message_body] AS XML)
FROM [YourQueue]);

Jonathan Kehayias (11/10/2012)

---

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,