Tracking Database File AutoGrowth with Event Notifications

  • Jonathan Kehayias

    One Orange Chip

    Points: 26778

    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.

    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]

  • John Esraelo-498130

    SSCertifiable

    Points: 5894

    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

    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

    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.

    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]

  • PFlorenzano-641896

    SSCommitted

    Points: 1643

    Thank you for your feedback Jonathan.

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

    Pete

Viewing 5 posts - 16 through 20 (of 20 total)

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