I have a SQL Server 2008 to administer and I found out that there were many sql agent jobs that were no supposed to run and that were generating errors each time they fired. The common thread amongst the agent jobs were they were owned by a domain account that was no longer valid. The agent jobs all had GUIDs for the name so viewing the list of jobs was not very helpful. I wrote this script so I could first view the agent jobs that I wanted to delete, then I could uncomment line 32 and comment out line 33 when I wanted to actually call the stored procedure that purged the jobs. You enter the user's name you want to view/delete jobs for on line 12 and control the view/delete process on lines 32 and 33.
I work in a fairly large and unwieldy SQL Server shop. Our environment is the wild west. I have DBAs and Developers on my team that create SQL Server Agent Jobs all of the time and there is never an operator setup to email anyone based on a failure condition. I know the right solution here is to "tame the wild west" by locking down the environment and setting up a change management process, but I think that is a topic for another day. Can you give me some idea of how to identify the new recently created SQL Server Agent Jobs and Jobs without an operator setup to email us for a failure condition? I would like to receive emails when either of these conditions occur. Check out the solution to this tip to learn how.