June 21, 2010 at 11:46 am
I have two shared schedules (quarterly; scheduled to run on 1/1, 4/1, 7/1, 10/1 and semi-annually; running on 1/1 and 7/1). According to the Report Manager both schedules appear to have executed on 2/1 and not on 1/1. I'm a new hire so I don't have the relevant history but is it possible/feasible that some kind of stoppage of the Reporting service or SQL Agent service (on 1/1) may have caused these schedules to have been missed and then run on 2/1? I highly doubt anyone was here at 5:00am on 2/1 which is when these schedules were executed so for now I'm ruling out the possibility that they were executed manually. What's of greater concern is that within Report Manager both schedules show a Next Run date of 2/1/2011 (instead of 7/1/2010).
Any assistance or thoughts you are willing to share would be appreciated!
Chris.
June 21, 2010 at 11:58 am
Hard to tell from what is provided. Can you script the jobs and their schedules and post that code? Maybe that would help us see what is going on.
June 21, 2010 at 12:22 pm
Hi Lynn,
Thanks for your response. Here's the job scripts for the two schedules I mentioned. I wasn't aware that the underlying jobs were managed by SQL Server Agent so I've already learned something new. Is this what you were looking for?
Semi-Annual Schedule
USE [msdb]
GO
/****** Object: Job [E0549CEB-6050-42E6-B3CC-AFE624D8BFF7] Script Date: 06/21/2010 12:08:26 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Report Server] Script Date: 06/21/2010 12:08:26 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Report Server' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Report Server'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'E0549CEB-6050-42E6-B3CC-AFE624D8BFF7',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'This job is owned by a report server process. Modifying this job could result in database incompatibilities. Use Report Manager or Management Studio to update this job.',
@category_name=N'Report Server',
@owner_login_name=N'NT AUTHORITY\NETWORK SERVICE', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [E0549CEB-6050-42E6-B3CC-AFE624D8BFF7_step_1] Script Date: 06/21/2010 12:08:26 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'E0549CEB-6050-42E6-B3CC-AFE624D8BFF7_step_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'TSQL',
@command=N'exec ReportServer.dbo.AddEvent @EventType=''TimedSubscription'', @EventData=''ec4276f5-0e6f-4818-b9a0-f74b28827fa7''',
@database_name=N'master',
@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'Schedule_1',
@enabled=1,
@freq_type=16,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=12,
@active_start_date=20090101,
@active_end_date=99991231,
@active_start_time=50000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Schedule_2',
@enabled=1,
@freq_type=16,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=12,
@active_start_date=20090701,
@active_end_date=99991231,
@active_start_time=50000,
@active_end_time=235959
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:
Quarterly Schedule
USE [msdb]
GO
/****** Object: Job [577E43BA-5CC9-4B0F-AE7A-4D47A663A466] Script Date: 06/21/2010 12:12:04 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Report Server] Script Date: 06/21/2010 12:12:04 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Report Server' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Report Server'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'577E43BA-5CC9-4B0F-AE7A-4D47A663A466',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'This job is owned by a report server process. Modifying this job could result in database incompatibilities. Use Report Manager or Management Studio to update this job.',
@category_name=N'Report Server',
@owner_login_name=N'NT AUTHORITY\NETWORK SERVICE', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [577E43BA-5CC9-4B0F-AE7A-4D47A663A466_step_1] Script Date: 06/21/2010 12:12:05 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'577E43BA-5CC9-4B0F-AE7A-4D47A663A466_step_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'TSQL',
@command=N'exec ReportServer.dbo.AddEvent @EventType=''SharedSchedule'', @EventData=''577e43ba-5cc9-4b0f-ae7a-4d47a663a466''',
@database_name=N'master',
@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'Schedule_1',
@enabled=1,
@freq_type=16,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=12,
@active_start_date=20070101,
@active_end_date=99991231,
@active_start_time=60000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Schedule_2',
@enabled=1,
@freq_type=16,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=12,
@active_start_date=20070401,
@active_end_date=99991231,
@active_start_time=60000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Schedule_3',
@enabled=1,
@freq_type=16,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=12,
@active_start_date=20060713,
@active_end_date=99991231,
@active_start_time=60000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Schedule_4',
@enabled=1,
@freq_type=16,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=12,
@active_start_date=20061001,
@active_end_date=99991231,
@active_start_time=60000,
@active_end_time=235959
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:
June 21, 2010 at 1:14 pm
One additional piece of information. I looked at a third shared schedule we use for monthly reports. Interestingly this schedule did run on 1/1 around the same time as the quarterly and semi-annual schedules were scheduled to run. This would tend to disprove my initial thoughts around a stopped service impacting these schedules.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply