SQL Agent Jobs – When Success is Failure1 min read

Have you ever run across an issue that made you take a step back and scratch your head?

Recently, I had this exact situation hit my Inbox. One of the development servers started alerting due to the Index Maintenace job failing. The first day, I ignored it because it was a development server. On day two, the alert looked similar, but I decided to let it go. On the third day, I decided to look into the job failure.

What are your troubleshooting steps when a job failure is reported?

  1. Open SQL Agent Job History for the failed job
  2. Look at the SQL Server Log
  3. This was an Ola job, so look at the CommandLog table
  4. Look at the text log file stored on the file system
  5. Open the job step and get the code being executed and run in a new query window

Now that you and I have performed the same steps and found no smoking gun, where do you go next?

After two more days of thinking about it in the back of my mind and letting a weekend go by, I just opened the Agent Job and started poking around.

Do you see what I see? That does not look right!

Who in the world not only went in and changed my job but changed it to a setting that makes no sense. Oh, how I wouldn’t say I like letting developers have elevated privileges.

In the end, it was a mistake and not done intentionally. The moral of the story is that sometimes the most basic overlooked setting can cause you heartburn when troubleshooting.

Leave a Reply

Your email address will not be published. Required fields are marked *