SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Tracking Database File AutoGrowth with Event Notifications


Tracking Database File AutoGrowth with Event Notifications

Author
Message
John Esraelo-498130
John Esraelo-498130
SSC Eights!
SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)

Group: General Forum Members
Points: 841 Visits: 1030
Hi Pete,
Here is a stored procedure that I use for notifying me on an existing database if any object is created, modified, etc.
Hope it helps and I understood your question correctly. Of course this USP needs to be part of the rest of object for notifications purposes.
(see attached)

And, thank you again Jonathan, I have been using this methodology database management, failed logins, autogrowth and DBuser Events..

Cheers,
John Esraelo
Attachments
John Esraelo-498130
John Esraelo-498130
SSC Eights!
SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)

Group: General Forum Members
Points: 841 Visits: 1030
I went ahead and created a table in one of the databases and since the USP I attached earlier looks over the server then generated a notification right away and sent it to me.

-- =====================================================
to me
Audit DB Mgmt Event Occured::
ApplicationName Microsoft SQL Server Management Studio - Query
DatabaseName ADMIN
DBUserName dbo
EventSubClass Alter
HostName D003951
LoginName johne
NTDomainName
NTUserName
ObjectName dbo
OwnerName dbo
ServerName companySQL
SessionLoginName johne
StartTime 2013-12-11T12:28:17.190
Success 1
TextData create table removemenow (field1 int null)

Cheers,
John Esraelo
PFlorenzano-641896
PFlorenzano-641896
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 Visits: 574
Thank you John,

I'll give it a try.

Pete
John Esraelo-498130
John Esraelo-498130
SSC Eights!
SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)

Group: General Forum Members
Points: 841 Visits: 1030
Pete,

BTW:: I have all my objects pertaining this whole event notification process in my MSDB.. this way I don't have to worry about the permissions and restrictions.
just thought to share this with you

Cheers,
John Esraelo
PFlorenzano-641896
PFlorenzano-641896
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 Visits: 574
I'm also utilizing MSDB, my issue is the notifications aren't working for existing databases, even though service broker is enabled on these database. Doesn't make sense to me, but I'll figure it out.
Jonathan Kehayias
Jonathan Kehayias
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2938 Visits: 1807
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

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
John Esraelo-498130
John Esraelo-498130
SSC Eights!
SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)

Group: General Forum Members
Points: 841 Visits: 1030
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
PFlorenzano-641896
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 Visits: 574


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

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
Jonathan Kehayias
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2938 Visits: 1807
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
PFlorenzano-641896
PFlorenzano-641896
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 Visits: 574
Thank you for your feedback Jonathan.

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

Pete
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search