Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Getting Started with SQL Server Event Notifications Expand / Collapse
Author
Message
Posted Wednesday, December 23, 2009 11:24 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 9:58 AM
Points: 2,280, Visits: 3,069
Great Job Jonathan! I look forward to part 2.



My blog: http://jahaines.blogspot.com
Post #838690
Posted Wednesday, December 23, 2009 8:05 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, December 15, 2014 7:14 PM
Points: 2,693, Visits: 1,223
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
Post #838835
Posted Wednesday, December 23, 2009 8:54 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, November 3, 2014 2:42 PM
Points: 1,683, Visits: 1,798
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
Post #838839
Posted Thursday, December 24, 2009 10:19 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 3, 2011 7:44 AM
Points: 151, 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.
Post #839011
Posted Friday, July 6, 2012 2:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 2:52 PM
Points: 5, Visits: 103
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
Post #1326322
Posted Friday, July 6, 2012 4:42 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, November 3, 2014 2:42 PM
Points: 1,683, Visits: 1,798
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
Post #1326356
Posted Saturday, November 10, 2012 1:53 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 2:58 PM
Points: 531, Visits: 965
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
Post #1383394
Posted Saturday, November 10, 2012 2:22 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, November 3, 2014 2:42 PM
Points: 1,683, Visits: 1,798
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
Post #1383395
Posted Saturday, November 10, 2012 3:29 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 2:58 PM
Points: 531, Visits: 965
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
Post #1383399
Posted Saturday, November 10, 2012 5:08 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 2:58 PM
Points: 531, Visits: 965
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
Post #1383402
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse