Snapshot Agent Schedule - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Replication (2008)

Snapshot Agent Schedule

Author

Topic

StellarSC
Starting Member

2 Posts

Posted - 2012-02-10 : 04:41:20

I setup my snapshot agent to have a schedule as follow:
Occurs every day every 2 hour(s) between 12:00:00 AM and 11:59:59 PM. Schedule will be used starting on 2/10/2012.
But it keeps on running every minute.

What am I missing?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-02-10 : 15:43:44

Right-click the snapshot job, choose script job as create to clipboard and post it here. My guess is that you set it up to run every minute.

StellarSC
Starting Member

2 Posts

Posted - 2012-02-13 : 01:35:35

USE [msdb]
GO

/****** Object: Job [506ASMA-ANSMasterStore-ANSMasterStoreToANSManagemen-14] Script Date: 02/13/2012 08:33:04 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [REPL-Snapshot] Script Date: 02/13/2012 08:33:04 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'REPL-Snapshot' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'REPL-Snapshot'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'506ASMA-ANSMasterStore-ANSMasterStoreToANSManagemen-14',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'REPL-Snapshot',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Snapshot Agent startup message.] Script Date: 02/13/2012 08:33:05 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Snapshot Agent startup message.',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'sp_MSadd_snapshot_history @perfmon_increment = 0, @agent_id = 14, @runstatus = 1,
@comments = N''Starting agent.''',
@server=N'506ASMA',
@database_name=N'Distribution2008',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Run agent.] Script Date: 02/13/2012 08:33:05 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run agent.',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=10,
@retry_interval=1,
@os_run_priority=0, @subsystem=N'Snapshot',
@command=N'-Publisher [506ASMA] -PublisherDB [ANSMasterStore] -Distributor [506ASMA] -Publication [ANSMasterStoreToANSManagement] -ReplicationType 2 -DistributorSecurityMode 1 ',
@server=N'506ASMA',
@database_name=N'Distribution2008',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Detect nonlogged agent shutdown.] Script Date: 02/13/2012 08:33:05 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Detect nonlogged agent shutdown.',
@step_id=3,
@cmdexec_success_code=0,
@on_success_action=2,
@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'sp_MSdetect_nonlogged_shutdown @subsystem = ''Snapshot'', @agent_id = 14',
@server=N'506ASMA',
@database_name=N'Distribution2008',
@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'Replication agent schedule.',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=2,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20120210,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'd90ce210-39f2-4692-b025-eaa71669207f'
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

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-02-13 : 12:16:26

Look for something else calling it. Can run a profiler trace for sp_start_job

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources