Post reply

Initialize New Subscription

  • May 10, 2012 at 1:31 am

    #260733

    Hi all,

    I have created a new merge subscription to a pre-existing publication. The publication is partitioned, via article filters using the host_name value. Executing selects based on the article filters returns the expected set of data. The subscriber server is a clean db copy of the publisher, with no data.

    When I attempt to initialize the subscription, the synchronization monitor shows the following sequence of messages:

    Retrieving subscription information

    The process is waiting for a response from the query '{call sys.sp_MSget_subscriber_partition_id (?,?,?)}'

    The merge process failed to execute a query because the query timed out. If this failure continues, increase the query timeout for the process. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.

    Then a sequence of Thread xx will wait... messages until finally the process fails with:

    The replication Merge Agent failed to retrieve subscription partitioning information from the Publisher. When troubleshooting, restart the agent with a higher value for -HistoryVerboseLevel and check the output log file for errors. Correct any database engine conditions that may be causing internal replication stored procedures to fail.

    When I look at the details for the new subscription in sysmergesubscriptions, there is no partition_id populated. The subscription has a status value of 0. No snapshot agent is created for the new subscription.

    I have tried to generate the partition via the publication properties > data partitions > generate the selected snapshots now option, but this just hangs.

    I've tried deleting the subscription and recreating it with the initialize immediately option, as well as using a script to generate the subscription and then attempting to initialize on first sync. The "automatically define a partition and generate a snapshot if needed when a new subscriber tries to synchronize" option is checked. Same problem each time.

    I'm new to replication. Clearly I've got something wrong in the initial setup. Any advice as to where to look would be appreciated.

    Regards, Iain

  • May 10, 2012 at 8:07 am

    #1485725

    Ah, problem solved. Adding the new subscription is only half the job. Manually running the creation of the partition and snapshot agent job seems to do the trick:

    -- create the partition

    exec sp_addmergepartition

    @publication = N'My_Publication',

    @host_name = N'My_Host_Name'

    -- return value is partition id for the newly created partition

    -- Create the filtered data snapshot job, and use the returned

    -- information to start the job.

    exec sp_adddynamicsnapshot_job

    @publication = N'My_Publication',

    @host_name = N'My_Host_Name'

    -- returns id, dynamic_snapshot_jobname, dynamic_snapshot_jobid

    exec msdb..sp_start_job @job_name = 'My_Returned_dynamic_snapshot_jobname'

    Hopefully this may be of use to someone else in the future...

Viewing 2 posts - 1 through 1 (of 1 total)

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