Getting Started with SQL Server Event Notifications

Question

Getting Started with SQL Server Event Notifications

Viewing 14 posts - 16 through 28 (of 28 total)

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

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

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/%5B/url%5D

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 2

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 One Orange Chip Points: 26778 More actions · Answer 3

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,

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 4

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 SSCertifiable Points: 5914 More actions · Answer 5

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

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 6

so much for my findings 😉 this tells me that my audit argument in the paragraph may be correct.

http://msdn.microsoft.com/en-us/library/ms182454%28v=sql.90%29.aspx

alright, the search of other possible areas continues..

Cheers,
John Esraelo

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

Send me the scripts for the Queue, Service, Event Notification, and Activation Procedure by email through my blog. Let me know what specific version of SQL Server you are running, and the Edition, or just copy/paste the output of SELECT @@VERSION into the message so that I can match your environment and I'll see if I can repro this and offer a complete fix.

Did you dump all of the messages from the queue and look at them to see if there is a problem with one?

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 8

I used your TEST DB creation method, generated 3 statements in the queue.. viewed the content of the queue table did not look any strange or anything like that.. also the queue captured some statements from another DB's transactional backup..

then I placed couple of email notifications as an echo print for debugging in the USP..

and I was just checking the email to see how far in the USP the process go in..

create PROCEDURE [dbo].[USP_ProcessAuditDatabaseManagementEvents]

WITH EXECUTE AS OWNER

AS

DECLARE @message_body XML;

DECLARE @message_sequence_number INT;

DECLARE @dialog UNIQUEIDENTIFIER;

DECLARE @email_message NVARCHAR(MAX);

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'LocalServer', -- your defined email profile

@recipients = 'JohnE.SQL@gmail.com', -- your email

@subject = 'Audit Database Management Event Notification',

@body = 'echo print to the message_body in USP';

WHILE ( 1 = 1 )

BEGIN

BEGIN TRANSACTION;

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'LocalServer', -- your defined email profile

@recipients = 'JohnE.SQL@gmail.com', -- your email

@subject = 'Audit Database Management Event Notification',

@body = 'after reading a records ';

-- Receive the next available message FROM the queue

WAITFOR

(

RECEIVE TOP(1) -- just handle one message at a time

@message_body=CAST(message_body AS XML)

FROM dbo.AuditDatabaseManagementEventQueue

), TIMEOUT 1000; -- if queue empty for 1 sec, give UPDATE AND GO away

sure, no problem, I will send you the code..

it is basically like the other ones that were adapted from yours and they work fabulously ..

that's why my suspicion was on audit_database_management...etc.. class / event name..

Cheers,
John Esraelo

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 9

Jonathan,

Wasn't able to find your email link on the blog.. but here is the code..

as you notice that I have commented out most majority of the elements to be emailed ..

I have a suspicion, as I mentioned before, on the AUDIT_DATABASE_MANAGEMENT_EVENT

and here is the version

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)

Jul 9 2008 14:43:34

Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)

-- Using msdb prevents the need for certificate signing the

-- activation procedure to execute sp_send_dbmail across

-- databases

USE [msdb];

GO

-- Drop the notification if it exists

IF EXISTS ( SELECT *

FROM sys.server_event_notifications

WHERE name = N'CaptureAuditDatabaseManagementEvents' )

BEGIN

DROP EVENT NOTIFICATION CaptureAuditDatabaseManagementEvents ON SERVER;

END

-- Drop the route if it exists

IF EXISTS ( SELECT *

FROM sys.routes

WHERE name = N'AuditDatabaseManagementEventRoute' )

BEGIN

DROP ROUTE AuditDatabaseManagementEventRoute;

END

-- Drop the service if it exists

IF EXISTS ( SELECT *

FROM sys.services

WHERE name = N'AuditDatabaseManagementEventService' )

BEGIN

DROP SERVICE AuditDatabaseManagementEventService;

END

-- Drop the queue if it exists

IF EXISTS ( SELECT *

FROM sys.service_queues

WHERE name = N'AuditDatabaseManagementEventQueue' )

BEGIN

DROP QUEUE AuditDatabaseManagementEventQueue;

END

-- Create a service broker queue to hold the events

CREATE QUEUE [AuditDatabaseManagementEventQueue]

WITH STATUS=ON;

GO

-- Create a service broker service receive the events

CREATE SERVICE [AuditDatabaseManagementEventService]

ON QUEUE [AuditDatabaseManagementEventQueue] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

GO

-- Create a service broker route to the service

CREATE ROUTE [AuditDatabaseManagementEventRoute]

WITH SERVICE_NAME = 'AuditDatabaseManagementEventService',

ADDRESS = 'LOCAL';

GO

-- Create the event notification to capture the events

CREATE EVENT NOTIFICATION [CaptureAuditDatabaseManagementEvents]

ON SERVER

WITH FAN_IN

FOR AUDIT_DATABASE_MANAGEMENT_EVENT

TO SERVICE 'AuditDatabaseManagementEventService', 'current database';

GO

-- ==========================================================================================

USE [msdb];

GO

SELECT

ApplicationName =message_body.value('(/EVENT_INSTANCE/ApplicationName )[1]','nvarchar(32)')

,DatabaseName=message_body.value('(/EVENT_INSTANCE/DatabaseName )[1]','nvarchar(32)')

,DBUserName=message_body.value('(/EVENT_INSTANCE/DBUserName )[1]','nvarchar(32)')

,EventSubClass=message_body.value('(/EVENT_INSTANCE/EventSubClass )[1]','int' )

, (case message_body.value('(/EVENT_INSTANCE/EventSubClass )[1]','int' ) when 1 then 'Create' when 2 then 'Alter' when 3 then 'Drop' when 4 then 'Dump' when 11 then 'Load' else 'Undefined' end) SubClassNameDesc

,HostName=message_body.value('(/EVENT_INSTANCE/HostName )[1]','nvarchar(32)')

,LoginName=message_body.value('(/EVENT_INSTANCE/LoginName )[1]','nvarchar(32)')

,NTDomainName=message_body.value('(/EVENT_INSTANCE/NTDomainName )[1]','nvarchar(32)')

,NTUserName=message_body.value('(/EVENT_INSTANCE/NTUserName )[1]','nvarchar(32)')

,ObjectName=message_body.value('(/EVENT_INSTANCE/ObjectName )[1]','nvarchar(32)')

,OwnerName=message_body.value('(/EVENT_INSTANCE/OwnerName )[1]','nvarchar(32)')

,ServerName=message_body.value('(/EVENT_INSTANCE/ServerName )[1]','nvarchar(32)')

,SessionLoginName=message_body.value('(/EVENT_INSTANCE/SessionLoginName)[1]','nvarchar(32)')

,StartTime=message_body.value('(/EVENT_INSTANCE/StartTime )[1]','datetime2' )

,Success =message_body.value('(/EVENT_INSTANCE/Success )[1]','int' )

,TextData=message_body.value('(/EVENT_INSTANCE/TextData )[1]','nvarchar(32)')

FROM ( SELECT CAST(message_body AS XML) AS message_body

FROM [AuditDatabaseManagementEventQueue]

) AS Tab;

-- ==========================================================================================

-- Create the Activation Stored Procedure to Process the Queue

IF EXISTS ( SELECT *

FROM dbo.sysobjects

WHERE id = OBJECT_ID(N'[dbo].[USP_ProcessAuditDatabaseManagementEvents]')

AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )

DROP PROCEDURE [dbo].[USP_ProcessAuditDatabaseManagementEvents];

GO

create PROCEDURE [dbo].[USP_ProcessAuditDatabaseManagementEvents]

WITH EXECUTE AS OWNER

AS

DECLARE @message_body XML;

DECLARE @message_sequence_number INT;

DECLARE @dialog UNIQUEIDENTIFIER;

DECLARE @email_message NVARCHAR(MAX);

WHILE ( 1 = 1 )

BEGIN

BEGIN TRANSACTION;

-- Receive the next available message FROM the queue

WAITFOR

(

RECEIVE TOP(1) -- just handle one message at a time

@message_body=CAST(message_body AS XML)

FROM dbo.AuditDatabaseManagementEventQueue

), TIMEOUT 1000; -- if queue empty for 1 sec, give UPDATE AND GO away

-- If we didn't get anything, bail out

IF ( @@ROWCOUNT = 0 )

BEGIN

ROLLBACK TRANSACTION;

BREAK;

END

declare

@ApplicationNamenvarchar(32)--nvarchar

,@DatabaseNamenvarchar(32)--nvarchar

,@DBUserNamenvarchar(32)--nvarchar

,@EventSubClassint--int

,@HostNamenvarchar(32)--nvarchar

,@LoginNamenvarchar(32)--nvarchar

,@NTDomainNamenvarchar(32)--nvarchar

,@NTUserNamenvarchar(32)--nvarchar

,@ObjectNamenvarchar(32)--nvarchar

,@OwnerNamenvarchar(32)--nvarchar

,@ServerNamenvarchar(32)--nvarchar

,@SessionLoginNamenvarchar(32)--nvarchar

,@StartTimenvarchar(32)--datetime

,@Successint--int

,@TextDatanvarchar(32)--ntext

select

@ApplicationName=message_body.value('(/EVENT_INSTANCE/ApplicationName )[1]','nvarchar(32)')

,@DatabaseName=message_body.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(32)')

,@DBUserName=message_body.value('(/EVENT_INSTANCE/DBUserName)[1]','nvarchar(32)')

,@EventSubClass=message_body.value('(/EVENT_INSTANCE/EventSubClass)[1]','int' )

,@HostName=message_body.value('(/EVENT_INSTANCE/HostName)[1]','nvarchar(32)')

,@LoginName=message_body.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(32)')

,@NTDomainName=message_body.value('(/EVENT_INSTANCE/NTDomainName)[1]','nvarchar(32)')

,@NTUserName=message_body.value('(/EVENT_INSTANCE/NTUserName)[1]','nvarchar(32)')

,@ObjectName=message_body.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(32)')

,@OwnerName=message_body.value('(/EVENT_INSTANCE/OwnerName)[1]','nvarchar(32)')

,@ServerName=message_body.value('(/EVENT_INSTANCE/ServerName)[1]','nvarchar(32)')

,@SessionLoginName=message_body.value('(/EVENT_INSTANCE/SessionLoginName)[1]','nvarchar(32)')

,@StartTime=message_body.value('(/EVENT_INSTANCE/StartTime)[1]','nvarchar(32)')

,@Success=message_body.value('(/EVENT_INSTANCE/Success)[1]','int' )

,@TextData=message_body.value('(/EVENT_INSTANCE/TextData)[1]','nvarchar(32)')

-- Generate formatted email message

select

@email_message = 'Audit DB Mgmt Event Occured::'

--+ char(10) + 'ApplicationName'+@ApplicationName

--+ char(10) + 'DatabaseName'+@DatabaseName

--+ char(10) + 'DBUserName'+@DBUserName

--+ char(10) + 'EventSubClass'+(case @EventSubClass when 1 then 'Create' when 2 then 'Alter' when 3 then 'Drop' when 4 then 'Dump' when 11 then 'Load' else 'Undefined' end)

--+ char(10) + 'HostName'+@HostName

--+ char(10) + 'LoginName'+@LoginName

--+ char(10) + 'NTDomainName'+@NTDomainName

--+ char(10) + 'NTUserName'+@NTUserName

--+ char(10) + 'ObjectName'+@ObjectName

--+ char(10) + 'OwnerName'+@OwnerName

--+ char(10) + 'ServerName'+@ServerName

--+ char(10) + 'SessionLoginName'+@SessionLoginName

--+ char(10) + 'StartTime'+@StartTime

--+ char(10) + 'Success'+@Success

--+ char(10) + 'TextData'+@TextData

-- Send email using Database Mail

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'LocalServer', -- your defined email profile

@recipients = 'JohnE.SQL@gmail.com', -- your email

@subject = 'Audit Database Management Event Notification',

@body = @email_message;

-- Commit the transaction. At any point before this, we could roll

-- back. The received message would be back on the queue AND the

-- response wouldn't be sent.

COMMIT TRANSACTION;

END

GO

-- Alter the queue to use the activation procedure

ALTER QUEUE [AuditDatabaseManagementEventQueue]

WITH STATUS=ON,

ACTIVATION

(STATUS=ON,

PROCEDURE_NAME = [dbo].[USP_ProcessAuditDatabaseManagementEvents],

MAX_QUEUE_READERS = 1,

EXECUTE AS OWNER);

GO

Cheers,
John Esraelo

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

Your activation stored procedure is the problem. Before you configured the queue for activation did you test the procedure by executing it? I am going to bet not in this case. If you run the procedure as written it will provide the following error:

Msg 4121, Level 16, State 1, Procedure USP_ProcessAuditDatabaseManagementEvents, Line 47

Cannot find either column "message_body" or the user-defined function or aggregate "message_body.value", or the name is ambiguous.

Msg 266, Level 16, State 2, Procedure USP_ProcessAuditDatabaseManagementEvents, Line 47

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

The problem is the code below: