Getting Started with SQL Server Event Notifications

Question

Post reply

Getting Started with SQL Server Event Notifications

Jonathan Kehayias

One Orange Chip

Points: 26778
More actions
December 22, 2009 at 12:08 am

#207667

Comments posted to this topic are about the item Getting Started with SQL Server Event Notifications
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[/url]

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply

Scott Abrants SSCommitted Points: 1503 More actions · Answer 1

Very nice work. Well written and very useful.

Thank you

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Nice Article

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ggoble Hall of Fame Points: 3337 More actions · Answer 3

So if I want to implement this on another databases, I would have to create the stored procedure in each database and then sign the procedure with the cert correct?

Also if I am currently using db mail in other stored procedures will I have to sign the certificate to all of those as well for them to continue to work?

Stan_Segers SSC-Addicted Points: 483 More actions · Answer 4

You only need to set up the Event Notification for the database (or server). The stored procedure is only needed in the database where the queue resides. You could send the events to another database or even another server. Also, you are not required to use signed procedures.

I did a blog-post some time ago to catch deadlocks with event notification. This shows you how to handle server level events. Deadlock Alerts Trough Event Notification and doesn't use signed procedures.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 5

greggoble2 (12/22/2009)
So if I want to implement this on another databases, I would have to create the stored procedure in each database and then sign the procedure with the cert correct?
Also if I am currently using db mail in other stored procedures will I have to sign the certificate to all of those as well for them to continue to work?

Trace events are server scoped, so you don't need multiple Notifications per database for this. If you used DDL Events at the database level, it would have to be created per database, and to send email through queue activation from that database with DatabaseMail would require signing it with a certificate as the most secure method. I covered the purpose behind using certificate signing to access DatabaseMail from a non-msdb database on my article last week Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail [/url].

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

Stan_Segers (12/22/2009)
You only need to set up the Event Notification for the database (or server). The stored procedure is only needed in the database where the queue resides. You could send the events to another database or even another server. Also, you are not required to use signed procedures.
I did a blog-post some time ago to catch deadlocks with event notification. This shows you how to handle server level events. Deadlock Alerts Trough Event Notification and doesn't use signed procedures.

You can create the server level items in msdb to get around the signed procedure but since that isn't a recommended practice, I don't offer ideas like that in online articles. It is one way to go about it though. I keep these kinds of things in DBA_DATA database on my servers personally, but everyone has their own methods.

This is the first in a series I have written on using Event Notifications and there is an article on capturing Deadlock Graphs pending publication, though I take things a bit further in my article than you did on your blog post by parsing the XML and retrieving additional information regarding the executing statements like their query plans which are important in deadlock analysis.

ggoble Hall of Fame Points: 3337 More actions · Answer 7

ggoble

Hall of Fame

Points: 3337

December 22, 2009 at 3:40 pm

#1095742

Thanks for the info! Great stuff!

@SQLNumpty SSCrazy Points: 2940 More actions · Answer 8

Excellent article. Looking forward to more like it !!!!! 🙂

louis.young Valued Member Points: 53 More actions · Answer 9

I've copied the T-SQL from this article exactly and executed it. All works fine, but when I view the queue:

SELECT *

FROM EventNotificationQueue

there are no records. What am I doing wrong?

Adam Haines SSC-Insane Points: 23217 More actions · Answer 10

Great Job Jonathan! I look forward to part 2.

My blog: http://jahaines.blogspot.com

philcart SSC-Forever Points: 47794 More actions · Answer 11

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.

--------------------
Colt 45 - the original point and click interface

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 12

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

Baskar B.V SSCrazy Points: 2823 More actions · Answer 13

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.

[font="Arial"]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.[/font]

vinay.kumar SSC Rookie Points: 33 More actions · Answer 14

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