2

I had a backup maintenance job setup for all system databases. I also have another job that sends me an email whenever a job fails.

After receiving notification of the system database backup job failure, I noticed that the maintenance job was no longer there. Now, I've setup hundreds of maintenance jobs and they don't normally just disappear. I'm guessing that it was deleted.

Are there any logs I can monitor that captures activity like that?

I know ideally, the solution would be to lock down the permissions but it's would be very difficult to do that in this environment as well as DBA's sometime make mistakes. But at least a DBA would know how to recreate the job - hopefully.

Thanks!

  • You're looking for someplace that would log someone deleting a maintenance plan? – Erik Darling Jul 28 '17 at 14:39
  • Correct - that's exactly what I'm looking for. – ronniej962 Jul 28 '17 at 15:13
  • No, I don't believe that's logged anywhere. Your best best in this case might be to find out when your last full backup was taken. WITH backups AS ( SELECT b.database_name, MAX(b.backup_finish_date) AS last_full FROM msdb.dbo.backupset AS b WHERE b.type = 'D' GROUP BY b.database_name ) SELECT *, DATEDIFF(DAY, backups.last_full, GETDATE()) AS between_days FROM backups; – Erik Darling Jul 28 '17 at 15:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.