Have you ever encountered a situation where your SQL Server maintenance plan job is not running as per the scheduled time? This can be a frustrating issue to deal with, especially when you rely on these jobs to perform important tasks like taking transaction log backups.

In this article, we will discuss a real-life scenario where a client faced a similar problem and how it was resolved. Let’s dive in!

The Problem

One of my clients contacted me with a complaint that their scheduled maintenance plan job, which was supposed to take a transaction log backup at 10 PM, was not running. When we checked the job history, there was no record of any execution. This issue seemed trivial at first, but it turned out to be more complex than anticipated.

Upon further investigation, we asked the client to share the SQLAgent.out files from the LOG folder. These files contain valuable information about the SQL Server Agent’s activities. After analyzing the file, we found some interesting error messages:

2016-06-29 20:00:00 – ! [298] SQLServer Error: 229, The EXECUTE permission was denied on the object ‘sp_sqlagent_log_jobhistory’, database ‘msdb’, schema ‘dbo’. [SQLSTATE 42000] (ConnExecuteCachableOp)
2016-06-29 20:10:36 – ! [298] SQLServer Error: 229, The SELECT permission was denied on the object ‘sysjobschedules’, database ‘msdb’, schema ‘dbo’. [SQLSTATE 42000] (SaveAllSchedules)
2016-06-29 20:10:36 – ! [298] SQLServer Error: 229, The UPDATE permission was denied on the object ‘sysjobschedules’, database ‘msdb’, schema ‘dbo’. [SQLSTATE 42000] (SaveAllSchedules)
2016-06-29 20:10:36 – ! [376] Unable to save 1 updated schedule(s) for job T-log Backup 10 PM.Subplan_1

These error messages indicated that there were permission issues related to the SQL Server Agent account. The account did not have sufficient permissions to execute certain operations required for the maintenance plan job.

The Solution

To resolve the issue, we captured a profiler trace while saving the maintenance plan and discovered that the SQL Agent account did not have the necessary permissions. According to the SQL Server documentation, the account running the SQL Server Agent service must be a member of the following roles:

• The account must be a member of the sysadmin fixed server role.
• To use multiserver job processing, the account must be a member of the msdb database role TargetServersRole on the master server.

Upon further discussion with the client, they mentioned that this problem started occurring after they followed an article on the internet to enhance their security measures. This serves as a reminder to always exercise caution when implementing advice found online and to verify the reliability of the source.

Conclusion

Dealing with SQL Server maintenance plan job issues can be challenging, especially when they don’t run as scheduled. In this article, we explored a real-life scenario where a client faced a similar problem and learned how to resolve it by ensuring the SQL Agent account has the necessary permissions.

Remember, it’s important to thoroughly investigate the root cause of any issue and not solely rely on internet advice. Always consider the reliability of the source and verify the information before implementing any changes.

Thank you for reading! If you have any questions or would like to share your own experiences, feel free to leave a comment below.