October 26, 2011 at 8:31 am
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.
October 26, 2011 at 8:34 am
When you say that you set up maintenance plans, I assume you are referring to the wizard?
Jared
Jared
CE - Microsoft
October 26, 2011 at 8:44 am
yes, I did use the wizard to create the plans.
October 26, 2011 at 8:49 am
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
October 26, 2011 at 8:51 am
I am changing the times through the job.
October 26, 2011 at 8:57 am
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
October 26, 2011 at 8:58 am
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.
October 26, 2011 at 9:02 am
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.
October 26, 2011 at 9:04 am
If all else fails... Recreate the maintenance plan, delete the old one, and reschedule. Sometimes SQL can be quirky 🙂
Jared
Jared
CE - Microsoft
October 26, 2011 at 9:06 am
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
October 26, 2011 at 9:07 am
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?
October 26, 2011 at 9:09 am
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?
October 26, 2011 at 9:09 am
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]
October 26, 2011 at 9:18 am
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
October 26, 2011 at 9:23 am
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