Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
1  
Can you edit the sub-plan to do some additional logging in the first step? This might help you track down the user/hostname etc. that started the job. You could also consider putting the logic in the sub-plan inside an IF check, e.g. IF DATEPART(HOUR, GETDATE()) = 19 ... - this will at least prevent these rogue invocations from causing any problems. –  Aaron Bertrand Mar 27 at 19:13
    
I can edit the sub-plan, but how could I use it to figure out who submitted it? I thought running jobs were pretty divorced from their submitters. –  RBarryYoung Mar 27 at 19:24
1  
You may be able to deduce this by collecting all of the active connections at the time, perhaps looping through them with DBCC INPUTBUFFER, etc. Not for the light of heart, but you can do this in the ELSE so that it doesn't take up unnecessary resources in the meantime. You could also do this by making your own logging modifications to sp_start_job (while a system procedure, you can modify it). Just putting in the date check, however, resolves the real issue, if you don't care that every once in a while some rogue process (probably SharePoint) tries to execute the job... –  Aaron Bertrand Mar 27 at 19:35
    
I think your datepart logic is a little off - after 2 AM and after 6 AM? –  Aaron Bertrand Mar 27 at 20:01
    
@AaronBertrand Errp! Thanks.. –  RBarryYoung Mar 27 at 20:40

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
Ooh, good catch! I was going to suggest a trigger on sysjobhistory too (like I did here), since it captures this information in the message column... –  Aaron Bertrand Mar 27 at 19:56

My suggestions:

  1. Put in a check within the job step that makes sure the maintenance plan logic only executes when the job is started sometime between 7PM and 8PM:

    IF DATEPART(HOUR, SYSDATETIME()) = 19
    BEGIN
      ... maintenance plan commands here ...
    END
    

    This doesn't eliminate the noise that comes from whatever rogue process is interfering, but it does prevent the damage.

  2. Add a logging table, e.g.:

    USE msdb;
    GO
    CREATE TABLE dbo.JobAudit
    (
      hostname SYSNAME NOT NULL DEFAULT HOST_NAME(), 
      appname SYSNAME  NOT NULL DEFAULT PROGRAM_NAME(),
      dt DATETIME2     NOT NULL DEFAULT SYSDATETIME()
    );
    GO
    GRANT INSERT ON dbo.JobAudit TO [public];
    
  3. Look up the name/job_id of the job, and modify msdb.dbo.sp_start_job by adding this at the very beginning (which will capture failures too) or right before the sp_post_msx_operation logic (which should only capture successful starts):

    IF @job_id = 'guid' OR @job_name = N'job name'
    BEGIN
      INSERT dbo.JobAudit DEFAULT VALUES;
    END
    

    You could also add a trigger to the JobAudit table that e-mails you when something inserts (so that you don't have to monitor the table manually).

share|improve this answer
    
All good suggestions Aaron. However, it did get figured it out. Thanks for the help. –  RBarryYoung Mar 27 at 19:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.