SQL Agent jobs not running as scheduled

  • I am running SQL Server 2008 R2 SP1. This is a new install. I set up maintenance plans to backup the databases and tlogs. I then scheduled the jobs to run at 10 PM. The jobs ran once on the schedule. The next time they ran was 5:04 AM instead of 10 pm. There were some errors in the logs about the AD account (server is on different domain). I deleted the maintenance plans and jobs and recreated them using a sql login. I scheduled the jobs to run at 8 AM. The jobs ran fine. Then I changed the schedule to run at 10PM. The jobs did not run. No error messages in any of the logs, no record of the job even trying to run. I changed the schedule again to run at 10 AM, again the jobs ran fine. Changed the schedule back to 10 PM, no jobs ran. The Agent is run by the local/system account. I can also run the jobs manually with either my AD account or the sql login. :angry: This is going to be a new production server and we need to get this corrected. Thanks in advance for any advice.

  • When you say that you set up maintenance plans, I assume you are referring to the wizard?

    Jared

    Jared
    CE - Microsoft

  • yes, I did use the wizard to create the plans.

  • Sheila.Roscoe (10/26/2011)


    yes, I did use the wizard to create the plans.

    Are you changing the run times through the maintenance plan or directly through the job? I am asking so I can see what you are seeing.

    Thanks,

    Jared

    Jared
    CE - Microsoft

  • I am changing the times through the job.

  • So... Once the job is open, you are clicking on schedules, highlighting the schedule, then clicking edit. Schedule type is "recurring" and the enabled box is checked? Then... Occurs Daily and Recurs every 1 days. Daily frequency is "Occurs once at 10 PM"... Start date is <= Today's date and there is no end date.

    If all this is correct, under the general tab of the job is Enabled checked?

    Finally, if all these are correct are you sure that the job completed on its first run? You can click on Job History on the General tab of that job and look for the duration to see how long it ran.

    Thanks,

    Jared

    Jared
    CE - Microsoft

  • just fyi...this is a virtual server. We have another virtual server, same version of SQL, and the jobs have been running fine since creation of the jobs.

  • I have done everything as you stated. Yes I am sure the job ran successfully. I am not new to setting up maintenance plans. We have over 500 dbs across 10 servers.

  • If all else fails... Recreate the maintenance plan, delete the old one, and reschedule. Sometimes SQL can be quirky 🙂

    Jared

    Jared
    CE - Microsoft

  • Also, if you are just backing up dbs and tlogs, I find a stored proc or script within a job is much easier to maintain versus a package created by the maintenance wizard. Easier to solve problems as well.

    Jared

    Jared
    CE - Microsoft

  • I have done that 3 times now. I restarted the agent and when that did not work, I restarted SQL. Maybe we should just re-install SQL Server?

  • I have recreated the plans numerous times. I restarted the agent and when that did not make any difference, I restarted SQL Server. Maybe we should just reinstall SQL?

  • First let me point out that people trying to help out on this form are shooting blind. We have to start at the beginning and assume nothing. Honestly if I'm trouble shooting for people on my own team I would start at the same place that Jared did. All that being said, would you please script out your job/schedule and post them?

    And as other tests you could run have you tried 10:01 PM? or 10:30 PM? 9:30 PM etc? Have you checked to see if there is another job running at the same time that may be interfering? Its odd that it won't run when you set it to 10 PM but will run at other times.

    Kenneth

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • There are only 5 jobs running on the server now. They do not overlap. I have tried different times and still having issues. Here is my job scripted, you will see that I have again changed the time, now to 12 pm and am waiting to see the results

    USE [msdb]

    GO

    /****** Object: Job [MaintenancePlan DBbackup.Subplan_1] Script Date: 10/26/2011 11:16:04 ******/

    BEGIN TRANSACTION

    DECLARE @ReturnCode INT

    SELECT @ReturnCode = 0

    /****** Object: JobCategory [Database Maintenance] Script Date: 10/26/2011 11:16:04 ******/

    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)

    BEGIN

    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    END

    DECLARE @jobId BINARY(16)

    EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'MaintenancePlan DBbackup.Subplan_1',

    @enabled=1,

    @notify_level_eventlog=2,

    @notify_level_email=0,

    @notify_level_netsend=0,

    @notify_level_page=0,

    @delete_level=0,

    @description=N'No description available.',

    @category_name=N'Database Maintenance',

    @owner_login_name=N'bkupadmin', @job_id = @jobId OUTPUT

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    /****** Object: Step [Subplan_1] Script Date: 10/26/2011 11:16:04 ******/

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Subplan_1',

    @step_id=1,

    @cmdexec_success_code=0,

    @on_success_action=1,

    @on_success_step_id=0,

    @on_fail_action=2,

    @on_fail_step_id=0,

    @retry_attempts=0,

    @retry_interval=0,

    @os_run_priority=0, @subsystem=N'SSIS',

    @command=N'/Server "$(ESCAPE_NONE(SRVR))" /SQL "Maintenance Plans\MaintenancePlan DBbackup" /set "\Package\Subplan_1.Disable;false"',

    @flags=0

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'db backup schedule',

    @enabled=1,

    @freq_type=4,

    @freq_interval=1,

    @freq_subday_type=1,

    @freq_subday_interval=0,

    @freq_relative_interval=0,

    @freq_recurrence_factor=0,

    @active_start_date=20111025,

    @active_end_date=99991231,

    @active_start_time=120000,

    @active_end_time=235959,

    @schedule_uid=N'18adc9ba-750f-4128-8c93-14a26f2c168a'

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    COMMIT TRANSACTION

    GOTO EndSave

    QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

    EndSave:

    GO

  • Kenneth Fisher-475792 (10/26/2011)


    First let me point out that people trying to help out on this form are shooting blind. We have to start at the beginning and assume nothing. Honestly if I'm trouble shooting for people on my own team I would start at the same place that Jared did. All that being said, would you please script out your job/schedule and post them?

    And as other tests you could run have you tried 10:01 PM? or 10:30 PM? 9:30 PM etc? Have you checked to see if there is another job running at the same time that may be interfering? Its odd that it won't run when you set it to 10 PM but will run at other times.

    Kenneth

    I'm not sure why you think I was shooting blind, in fact not knowing the level of knowledge of the OP I was gathering all information about the set up that I could. All too many time people have a job disabled or even the schedule disabled. Clearly the OP has a good knowledge of SQL Server, but I did not know this at first.

    Jared

    Jared
    CE - Microsoft

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply