Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Getting Started with SQL Server Event Notifications


Getting Started with SQL Server Event Notifications

Author
Message
Adam Haines
Adam Haines
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2464 Visits: 3135
Great Job Jonathan! I look forward to part 2.



My blog: http://jahaines.blogspot.com
philcart
philcart
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2970 Visits: 1435
louis.young, One thing I can think of is to make sure you are in the same database that you created the queue in.


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

Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
Jonathan Kehayias
Jonathan Kehayias
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2020 Visits: 1807
philcart (12/23/2009)


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



Event Notifications and Extended Events are completely different animals in SQL Server. Extended Events are new in 2008 while Event Notifications exist in 2005, and the eventing for the two is very different. If you'd like more information on Extended Events, you can read my whitepaper on MSDN,

Using SQL Server 2008 Extended Events

as well as Paul Randal's article on Technet:

Advanced Troubleshooting with Extended Events

If you'd like some other references I have a tag on my blog for Extended Events posts I write:

http://sqlblog.com/blogs/jonathan_kehayias/archive/tags/Extended+Events/default.aspx

and so does Paul:

http://www.sqlskills.com/BLOGS/PAUL/category/Extended-Events.aspx

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Baskar B.V
Baskar B.V
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 341
Hi,

I am trying to create notification in server A and want the read the queue data using stored procedure from server B. Can you help me with sample article of how to achieve this? I am struck with creating notification in server A and making it to pass the data to server B. Where do i mention that the data should go to server B. Greatly appreciate your help.

BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.

vinay.kumar
vinay.kumar
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 108
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
Jonathan Kehayias
Jonathan Kehayias
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2020 Visits: 1807
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


Why would you do that? The conversation being reused by the Event Notification is normal behavior, and reuse of conversations can be a a major performance improvement.

http://rusanu.com/2007/04/25/reusing-conversations/

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
John Esraelo-498130
John Esraelo-498130
SSChasing Mays
SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)

Group: General Forum Members
Points: 601 Visits: 1030
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

Cheers,
John Esraelo
Jonathan Kehayias
Jonathan Kehayias
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2020 Visits: 1807
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,

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
John Esraelo-498130
John Esraelo-498130
SSChasing Mays
SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)

Group: General Forum Members
Points: 601 Visits: 1030
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,


Thank you Jonathan, I will give it a shot in a minute and keep you posted of the results .
thx again

Cheers,
John Esraelo
John Esraelo-498130
John Esraelo-498130
SSChasing Mays
SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)

Group: General Forum Members
Points: 601 Visits: 1030
FYI
I wonder if my AUDIT_DATABASE_MANAGEMENT_EVENT statement in the paragraph in below does not exist or typed incorrectly..
so, I am going to search on internet / msdn and see what I can find out ..


CREATE EVENT NOTIFICATION [CaptureAuditDatabaseManagementEvents]
ON SERVER
WITH FAN_IN
FOR AUDIT_DATABASE_MANAGEMENT_EVENT
TO SERVICE 'AuditDatabaseManagementEventService', 'current database';
GO

Cheers,
John Esraelo
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search