sp_AddMergePushSubscription_Agent fails with DBCC error

  • Hi,

    I have a SQL2005 publication that is merge replicated out to various subscribers (all SQL2005 Express/Workgroup). Recently, I have found that one of our subscribers has lost its merge distribution agent. It does not appear in the Replication Monitor list, neither does a merge agent job exist on the distribution server for this subscriber.

    The subscription is still listed in sysmergesubscriptions with Status = 1 (active). A snapshot agent job exists for this subscription.

    I have tried re-adding the agent by executing sp_AddMergePushSubscription_Agent with the following parameters:

    EXEC dbo.sp_AddMergePushSubscription_Agent

    @publication= <PublicationName>

    , @subscriber= <SubscriberName>

    , @subscriber_db= <DatabaseName>

    , @frequency_type= 2 -- on demand

    These parameters are all valid and correct.

    This generates the following error:

    Msg 2560, Level 16, State 9, Procedure sp_MSadd_merge_agent, Line 376

    Parameter 2 is incorrect for this DBCC statement.

    The statement has been terminated.

    Msg 515, Level 16, State 2, Procedure sp_add_job, Line 137

    Cannot insert the value NULL into column 'name', table 'msdb.dbo.sysjobs'; column does not allow nulls. INSERT fails.

    The source of the error appears to be a call to the undocumented DBCC addinstance command that is part of sp_AddMergePushSubscription_Agent

    Any suggestions for how I get this subscription back up and running? I am considering manually creating the merge agent job (by scripting out one of the other subscribers' job, see end of post), but this needs a parameter, agent_id, that I can't seem to find within the database. Although, looking at the application_name column of sysmergesubscriptions, this seems to follow a convention like this:

    SERVERNAME-DBNAME-PUBLICATIONNAME-SUBSCRIBERNAME-AGENT_ID

    So perhaps I could just use this?

    Although, I'm not sure if this would even solve the problem as I suspect that the job might need to be registered somewhere?

    Regards, Iain

    Job creation script:

    USE [msdb]

    GO

    /****** Object: Job [<Merge_Job_Name>] Script Date: 06/25/2013 14:49:53 ******/

    BEGIN TRANSACTION

    DECLARE @ReturnCode INT

    SELECT @ReturnCode = 0

    /****** Object: JobCategory [REPL-Merge] Script Date: 06/25/2013 14:49:53 ******/

    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'REPL-Merge' AND category_class=1)

    BEGIN

    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'REPL-Merge'

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    END

    DECLARE @jobId BINARY(16)

    EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'<Merge_Job_Name>',

    @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'REPL-Merge',

    @owner_login_name=N'distributor_admin', @job_id = @jobId OUTPUT

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    /****** Object: Step [Merge Agent startup message.] Script Date: 06/25/2013 14:49:54 ******/

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Merge 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_merge_history @perfmon_increment = 0, @agent_id = XXXX, @runstatus = 1,

    @comments = ''Starting agent.''',

    @server=N'<Distribution Server>',

    @database_name=N'distribution',

    @flags=0

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    /****** Object: Step [Run agent.] Script Date: 06/25/2013 14:49:54 ******/

    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'Merge',

    @command=N'-Publisher <Publication Server> -PublisherDB <PublicationDB> -Publication <Publication> -Subscriber <Subscriber> -SubscriberDB <PublicationDB> -Distributor [<Distribution Server>] -DistributorSecurityMode 1 -HostName <Subscriber> ',

    @server=N'<Distribution Server>',

    @database_name=N'distribution',

    @flags=0

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    /****** Object: Step [Detect nonlogged agent shutdown.] Script Date: 06/25/2013 14:49:54 ******/

    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 = ''Merge'', @agent_id = XXXX',

    @server=N'<Distribution Server>',

    @database_name=N'distribution',

    @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_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:

  • I figured updating this might help someone sometime.

    Creating the replication agent job manually didn't work. The job itself could be executed, but it didn't do anything and didn't result in the subscription reappearing in the Replication Monitor list. In the end, we had to drop and recreate the subscription, then manually edit the publisher sysmergesubscriptions record from the subscriber version of this table using this script:

    update b

    set recgen = a.sentgen

    , recguid = a.sentguid

    , sentgen = a.recgen

    , sentguid = a.recguid

    , status = 1

    from <MySubscriberServer>.<MyDBName>.dbo.sysmergesubscriptions a

    join sysmergesubscriptions b

    on a.pubid = b.pubid

    where a.pubid like 'B%' -- only relevant for my circumstances - use suitable criteria to limit to your publication

    and a.subscriber_server = '<MyPublisherServer>'

    and b.subscriber_server = '<MySubscriberServer>';

    update b

    set schemaversion = a.schemaversion

    , replicastate = a.replicastate

    from <MySubscriberServer>.<MyDBName>.dbo.sysmergesubscriptions a

    join sysmergesubscriptions b

    on a.pubid = b.pubid

    where a.pubid LIKE 'B%' -- only relevant for my circumstances - use suitable criteria to limit to your publication

    and a.subscriber_server = '<MySubscriberServer>'

    and b.subscriber_server = '<MySubscriberServer>';

    Obviously, this relies on having your subscriber server set up as a linked server on your publishing server.

    This allowed the subscription to be synchronised normally. Incidentally, this approach can also be used to unmark a subscription that has been marked for reinitialisation.

    Edit: typo

    Edit2: removed database name

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

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