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 ««12

Tracking Database File AutoGrowth with Event Notifications Expand / Collapse
Author
Message
Posted Wednesday, December 11, 2013 1:24 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 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


  Post Attachments 
ServerChangeMonitoringEventProcedure.docx (3 views, 21.89 KB)
Post #1522038
Posted Wednesday, December 11, 2013 1:31 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
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
Post #1522041
Posted Wednesday, December 11, 2013 1:44 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 25, 2014 5:48 AM
Points: 65, Visits: 403
Thank you John,

I'll give it a try.

Pete
Post #1522049
Posted Wednesday, December 11, 2013 1:50 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
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
Post #1522051
Posted Wednesday, December 11, 2013 1:56 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 25, 2014 5:48 AM
Points: 65, Visits: 403
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.
Post #1522053
Posted Wednesday, December 11, 2013 1:57 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:55 AM
Points: 1,708, Visits: 1,792
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
Post #1522054
Posted Wednesday, December 11, 2013 2:03 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
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
Post #1522056
Posted Wednesday, December 11, 2013 2:04 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 25, 2014 5:48 AM
Points: 65, Visits: 403


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.
Post #1522057
Posted Wednesday, December 11, 2013 2:16 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:55 AM
Points: 1,708, Visits: 1,792
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
Post #1522059
Posted Thursday, December 12, 2013 6:09 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 25, 2014 5:48 AM
Points: 65, Visits: 403
Thank you for your feedback Jonathan.

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

Pete
Post #1522269
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse