SQL Server Agent jobs are crucial to any SQL Server environment, as they are used for scheduling and performing critical business and operational tasks. For example, I’ve got several hundred SQL Server Agent jobs that are scheduled to run on a daily, weekly or monthly basis on our various SQL Servers. These jobs are very critical, and as a database administrator, my responsibility is to ensure that these jobs are running every day, or on a weekly, or monthly basis and no unauthorised changes have been made to these jobs. I also need to know, who and when a SQL Server Agent Job is created, deleted or modified on my SQL Server? Obviously this is not an easy task to perform manually and there are not many options available in SQL Server to monitor such kind of activity. As a result, I have decided to build my own monitoring solution that automated this task with the help of DML triggers, which helps me to monitor SQL Agent Job modification activity in real-time.

Checkout my article here, which shows step-by-step implementation of this monitoring solution, which performs real-time monitoring of SQL Server Agent Jobs creation, modification and deletion activities.

This article is published on SSWUG.org.