snapshot replication job starts immediately when created

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Replication » snapshot replication job starts immediately...

snapshot replication job starts immediately when created

Rate Topic

Display Mode

Topic Options

Author

Message

Kerry Conner-320974

Posted Tuesday, November 11, 2008 10:50 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, April 25, 2013 1:30 PM
Points: 5, Visits: 316

I have a snapshot replication job that copies most of the tables in a production database (on server SQL1) over to a reporting server (server SQL2) once per night. Both servers are running SQL 2000. This is a push subscription running from distributor and publisher SQL1 to subscriber SQL2.

When it is necessary to remove the subscription (for instance, when recently migrating SQL2 to new hardware) I run the following script and it seems to work fine.

exec sp_dropsubscription @publication = N'SQL1.OfficeDB', @article = N'all', @subscriber = N'SQL2', @destination_db = N'OfficeDB'

However, when I run the following script to add the subscription back, it results in problems.

exec sp_addsubscription @publication = N'SQL1.OfficeDB', @article = N'all', @subscriber = N'SQL2',
@destination_db = N'OfficeDB', @sync_type = N'automatic', @update_mode = N'read only', @offloadagent = 0,
@dts_package_location = N'distributor'

The system procedure adds a replication distribution job into the scheduler that starts running immediately.

The replication distribution job that is created has three steps:

1 - Distribution Agent startup message
2 - Run agent
3 - Detect nonlogged agent shutdown

The job detail on the second step, as programatically created by the procedure, is:

-Subscriber [SQL2] -SubscriberDB [OfficeDB] -Publisher [SQL1] -Distributor [SQL1] -DistributorSecurityMode 1 -PublisherDB [OfficeDB] -Continuous

I have learned to cancel the job as soon as running the 'sp_addsubscription' script. I'm certain that there are appropriate parameters to use with the 'sp_addsubscription' script that will schedule the jobs as desired but I'm apparently not smart enough to decipher them from BOL (not quite sure which parameters to make use of).

I have further discovered that if I remove the '-Continuous' from the script and then manually schedule the job appropriately (once per night at 2:15am), it works great.

Any recommendations on the sp_addsubscription parameters? Thank you in advance...

Post #600823

MarkusB

Posted Thursday, November 13, 2008 5:11 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 3:03 AM
Points: 4,218, Visits: 3,874

If you lookup 'sp_addsubscription' in books online, you will see that there are the parameters@frequency_type, frequency_interval, @active_start_date and @active_start_time_of_day.
These will create a schedule when to start replicating and how often.
http://msdn.microsoft.com/en-us/library/aa239405(SQL.80).aspx

Markus Bohse

Post #601995

« Prev Topic | Next Topic »

Permissions