This was one of the interesting issues I solved in many days. One of my clients contacted me and told that they have scheduled a maintenance plan to take t-log backup at 10 PM but it’s not running. When we look into the job history, it was not showing any history. Some problem statements like these are interesting because they look trivial and simple – yet they are convoluted and not straightforward to solve. Let us learn how to solve Error “Maintenance plan scheduled, but the job is not running as per schedule“
I asked them to show the problem so that I can see live and look at various things happening. I asked them to share LOG folder which contains SQLAgent.out files.
SQL SERVER – Where is ERRORLOG? Various Ways to Find ERRORLOG Location
When I looked into the file, I was able to find interesting messages like below.
2016-06-29 20:00:00 – !  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 – !  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 – !  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 – !  Unable to save 1 updated schedule(s) for job T-log Backup 10 PM.Subplan_1
So above was the problem in Agent log file at 8 PM. I asked to open maintenance plan to try to save schedule it again. As soon as we did that, there was no error raised, but the job was not reflecting that schedule.
Fix/ Solution / Workaround:
I captured profiler while saving the Maintenance plan and found that permission from the SQL Agent account was not sufficient as I was seeing the error 298.
As per documentation: (Select an Account for the SQL Server Agent Service)
The account that the SQL Server Agent service runs as must be a member of the following SQL Server 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.
Later client informed me that this all started happening when they followed an article on internet to harden the security.
Moral of the story: Never trust on internet advice as not everything would be true. Always look at author and check his/her reliability.
Reference: Pinal Dave (https://blog.sqlauthority.com)
I have an error when executing database backup in Maintenance Plan.
The account for my SQL Server Agent service is LocalSystem
Started: 5:29:17 PM
Could not load package “Maintenance Plans\backup @3am” because of error 0xC001404A.
Description: While trying to find a folder on SQL an OLE DB error was encountered with error code 0x80040E09
(The EXECUTE permission was denied on the object ‘sp_ssis_getfolder’, database ‘msdb’, schema ‘dbo’.).
Started: 5:29:17 PM
Finished: 5:29:17 PM
Elapsed: 0.047 seconds
I would take profiler and check the account used. Give proper permission and try.
Try setting the owner of the Job to sa
I’m having a problem with a backup maintenance plan as well. The plan have to stop a few services as the first step and kill open connections, which it does. It then does the database and transaction log backups. Then there are two steps to delete old BAK and TRN files. The very last step starts the services again which were previously stopped. The “connectors” between the last 3 steps are “Completion”. However, the last step doesn’t get run – it is logging to a text file and there is no indication at all of it even trying to run the last step.
Is there some way that the plan can set a “final” step, thus not attempting to run what looks like the last step in the workflow?
SQL Server 2016, SP2