I have a situation that has left me really scratching my head. This is a SQL Server 2008r2 instance, used as the database server for a substantial corporate SharePoint service. As my company is providing the DB support for it, one of the things I did was to setup some maintenance plans to take care of the usual things, including nightly full backups of all of the databases at 7PM.
This all works fine, except that it has occasionally also been running sometime in the early morning.
So far this has happened three times, once 11 days ago at 2:47AM, once 4 days ago at 3:43AM and once 2 days ago at 3:49AM. I can find no reason why it should be running at these sporadic random times.
This is causing significant problems for us, as the early morning is when the server hosting provider runs their backup and maintenance jobs. Their execution takes many times longer than normal and are sending out alerts and other notices for this, long IO times and various errors that appear to be because of conflicts with the host SAN/NAS mirroring and backups.
To summarize what I have already checked:
This is the SQL Agent Job running an extra unscheduled time, and NOT just the maintenance plan running twice within the same job.
This does not appear to be to be a VM clone of this node with a SQL Agent running a duplicate of this job against the real SQL Server. I conclude this because the executions are showing up in our SQL Agent's log. (if that's not definitive, let me know how to check it)
This is not a case of the Job just running over for a long time. The regular job runs and completes at the expected scheduled time every day (7PM).
Neither the Server, nor the SQL Server instance have been restarted during the last month.
The SQL Server Job in question has only one schedule, that is set for 7PM every day.
The Maintenance Plan has only one subplan, that shows exactly the same schedule as the Job does.
Any help greatly appreciated.
At Aaron's suggestion, I have added the following Execute SQL as the first task in the sub-plan:
IF (DATEPART(HOUR, GETDATE()) > 2) AND (DATEPART(HOUR, GETDATE()) < 6) RAISERROR('Job Running at the Wrong Time!', 16, 1) WITH LOG