March 27, 2014 at 9:33 am
I have a situation at a customer site, 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 with 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, not 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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 27, 2014 at 12:58 pm
The fact that it's sporadic is weird. Are there other jobs that run, that could trigger this job to run as well, after they are complete using an EXEC Sp_start_job statement? I've seen that before.
March 27, 2014 at 1:05 pm
JoshDBGuy (3/27/2014)
The fact that it's sporadic is weird. Are there other jobs that run that could trigger this job to run as well after they are complete using an EXEC Sp_start_job statement? I've seen that before.
Not as far as I know. Plus I can see for sure that there were no other Agent Jobs running at these times.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 27, 2014 at 1:15 pm
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?
March 27, 2014 at 1:49 pm
JoshDBGuy (3/27/2014)
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?
Wow, Gold Star Josh!
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).
Great catch! 😀
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 27, 2014 at 2:39 pm
Great, glad I could help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply