Tracking Database File AutoGrowth with Event Notifications

Question

Tracking Database File AutoGrowth with Event Notifications

Viewing 5 posts - 16 through 19 (of 19 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

PFlorenzano-641896 (12/11/2013)
Hello Jonathan,
Thank you for providing this article. I have a question on how to add this functionality to an 'existing' database within my organization.
For instance, the code you provided within the article works great when creating a new database, but how would this work with an existing database? I've enabled service broker on the database, but for some reason, no email are coming through when running an execution loop test.
Thanks,
Pete

If you create this outside of msdb, you have to use certificate based security to sign the activation stored procedure as explained in this article:

Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail[/url]

As explained in the comments in the code, I create all this stuff in msdb so that we don't have to deal with managing cross database security and certificate signatures.

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

Pete,

You already, probably, have done this but had to share that with you anyway.

There are times that the queues go down in case of some error what not..

Therefore, if that happens then one or more queues under MSDB.SERVICE BROKER.QUEUES.DBO.XXXX folder may have an arrow down so to speak ..

and sometimes a right click/enable might bring it back up online if the issue behind the scene were not critical..

Cheers,
John Esraelo

PFlorenzano-641896 SSCommitted Points: 1643 More actions · Answer 3

If you create this outside of msdb, you have to use certificate based security to sign the activation stored procedure as explained in this article:
Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail[/url]
As explained in the comments in the code, I create all this stuff in msdb so that we don't have to deal with managing cross database security and certificate signatures.

I am creating this within MSDB as stated in your following article:

http://www.sqlservercentral.com/articles/autogrowth/93229/

The ONLY difference is that I'm attempting to use an existing database during the Testing Event Notification instead of creating a new one.

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

PFlorenzano-641896 (12/11/2013)
I am creating this within MSDB as stated in your following article:
http://www.sqlservercentral.com/articles/autogrowth/93229/
The ONLY difference is that I'm attempting to use an existing database during the Testing Event Notification instead of creating a new one.

It doesn't matter if it is an existing database or a new one, as long as the Database Autogrow Event fires, you can test this by also running a Trace and collecting the same event, the notification should fire. If the trace event fires and the notification doesn't then you need to troubleshoot your setup, start by dropping everything and recreating it from the scripts in the article, and figure out what is broken in how you setup the Event Notifications side of the monitoring. IF the Trace Event fires, the Event Notification should, it doesn't discriminate existing versus new databases at all. You also have trigger autogrow, manually growing files doesn't fire that same event.

PFlorenzano-641896 SSCommitted Points: 1643 More actions · Answer 5

Thank you for your feedback Jonathan.

I'm going to conduct a series of tests today.

Pete