x

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, 2011 at 08:01 AM in Default

azaragoza gravatar image

azaragoza
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, 2011 at 08:32 AM azaragoza
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.

EDIT:

@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, 2011 at 10:52 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.9k 18 20 44

(comments are locked)
10|1200 characters needed characters left
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, 2011 at 11:17 AM

BradleySQL gravatar image

BradleySQL
555 3 8 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, 2011 at 11:23 AM ThomasRushton ♦
(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, 2011 at 08:14 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

(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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x33

asked: Feb 04, 2011 at 08:01 AM

Seen: 755 times

Last Updated: Feb 04, 2011 at 08:01 AM