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.
asked Feb 04 '11 at 08:01 AM in Default
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.
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?
answered Feb 06 '11 at 11:17 AM
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?
answered Feb 04 '11 at 08:14 AM