June 25, 2013 at 7:52 am
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:
July 2, 2013 at 11:05 am
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