How can I get notified by email when the maintenanceplans change

How can I get notified by email when the maintenanceplans change. In my organization many people have access to the SQL Servers with admin rights and they often change the manitenenaceplans and make them fail. I would like to get an email every time the maintenance plans change.

Thanks for any help, this is a major issue in my job.
more ▼

asked Feb 04 '11 at 08:01 AM in Default

azaragoza gravatar image

11 1 1 1

I have tried everything possible, but no one will admit to making changes and the managers will not persue it because of the politics. That is my reason for looking for some other way of dealing with the problem. We have SQL Severs 2000 thru 2008 on both 32 and 64 bit.
Feb 04 '11 at 08:32 AM azaragoza
(comments are locked)
10|1200 characters needed characters left

3 answers: sort newest
I haven't studied up on events much but does changing a maintenance plan write and event? If so could you create a notification on that event?
more ▼

answered Feb 06 '11 at 11:17 AM

BradleySQL gravatar image

555 3 5 9

You should be able to write an event monitor for this that the extended events system could use; however, that would only be an option on 2008 - @azaragoza indates that this requirement is for 2000&2005 servers as well.
Feb 06 '11 at 11:23 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

Have a look in the msdb database.

You could, in theory, put triggers on the sysjobs / sysjobsteps tables to notify you, and maybe take a look at the sysdtspackages / sysdtspackages90 tables (going from memory here...) There maybe others that are of use...

This, of course, assumes that your colleagues aren't just modifying a single SSIS package... in which case you would need to set up a filesystem monitor of some sort to look at the SSIS package files and notify you of changes.


@BradleySQL suggests Event monitoring. Which led me to think of setting up traces against insert/update statements on the tables I mention... OK, it wouldn't generate an immediate alert, but it would provide an audit trail.
more ▼

answered Feb 06 '11 at 10:52 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.4k 14 20 44

(comments are locked)
10|1200 characters needed characters left

If there are that many people who can make the changes then you should have a business procedure that states that any changes should be logged (electronic or physical) so that breaking changes can be identified and reversed.

What version of SQL Server are you using?
more ▼

answered Feb 04 '11 at 08:14 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 04 '11 at 08:01 AM

Seen: 647 times

Last Updated: Feb 04 '11 at 08:01 AM