SQL Server – Maintenance Plan Job Running at Unscheduled Times

maintenance-planssql serversql-server-2008-r2sql-server-agent

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:

  1. This is the SQL Agent Job running an extra unscheduled time, and NOT just the maintenance plan running twice within the same job.

  2. 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)

  3. 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).

  4. Neither the Server, nor the SQL Server instance have been restarted during the last month.

  5. The SQL Server Job in question has only one schedule, that is set for 7PM every day.

  6. 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

Best Answer

Well, mystery solved.

I had previously posted this at another site (SqlServerCentral.com), and then posted here after I had not gotten a response for several hours. While I was trying Aaron's suggestion here, a Josh posted the following there:

Did you check the history of the job to see how it was invoked? Usually you'll see something like "The job succeeded. The Job was invoked by Schedule XXXX (Schedule_X ). The last step to run was step 1 (XXXXXXXXXXXXXX)."

Does it look the same for each run?

Though I had previously scoured the Job's history, I had not specifically looked for that message. And lo and behold, the unscheduled ones have the following:

... The Job was invoked by Alert 1.  ...

So someone must have tagged this job to run after an Alert (likely the wrong job).

Naturally now I feel silly for not checking this before. But I post this here now, so that others in the future may benefit from my oversight.

Related Question