sql server replication: Hi

I was synchronizing two SQL Server 2k (SP3) using merge replication for the last 4 months. There was an error in one partiucular table at the publisher because of which synchronization had stopped for around 14-18 hours between these two servers. I fixed this table using the DBCC checktable with the REPAIR_DATA_LOSS option. No data as such was lost though

Now when I sychronize the servers I get the following error - "The merge process could not initialize the Publisher 'ECOSYSTEM:MLEcosystem'.

I am attaching the contents of the log file below -

________________________________________________________________________________________

________________________________________________________________________________________

I have tried changing the merge agent profile's Query Timeout, Login Timeout parameter and quite a few parameters to solve this problem as these two servers synchronize over the Internet

The MSmerge_genhistory at the subscriber has 7000 rows and the one at the publisher has around 8000 rows

Can someone please advise as to how to synchronize these servers

Thank you in advance

Regards
Manis

The log file for the above message is as follows

Microsoft SQL Server Merge Agent 8.00.76
Copyright (c) 2000 Microsoft Corporatio
Microsoft SQL Server Replication Agent: ECOSYSTEM-MLEcosystem-Production_MLEcosystem-ML-US-MLEcosystem-

Percent Complete:
Connecting to Subscriber 'ML-US
Connecting to Subscriber 'ML-US.MLEcosystem

Server: ML-U
DBMS: Microsoft SQL Serve
Version: 08.00.076
user name: db
API conformance:
SQL conformance:
transaction capable:
read only:
identifier quote char:
non_nullable_columns:
owner usage: 3
max table name len: 12
max column name len: 12
need long data len:
max columns in table: 102
max columns in index: 1
max char literal len: 52428
max statement len: 52428
max row size: 52428

[3/31/2004 9:48:45 AM]ML-US.MLEcosystem: {call sp_MSgetversion
[3/31/2004 9:48:45 AM]ML-US.MLEcosystem: {?=call sp_helpsubscription_properties (N'ECOSYSTEM', N'MLEcosystem', N'Production_MLEcosystem')
Distributor security mode: 0, login name: sa, password: ********
[3/31/2004 9:48:45 AM]ML-US.MLEcosystem: {?=call sp_helpsubscription_properties (N'ECOSYSTEM', N'MLEcosystem', N'Production_MLEcosystem')
Publisher security mode: 0, login name: sa, password: *********
Percent Complete:
Connecting to Distributor 'ECOSYSTEM
Connecting to Distributor 'ECOSYSTEM.

Server: ECOSYSTE
DBMS: Microsoft SQL Serve
Version: 08.00.076
user name: db
API conformance:
SQL conformance:
transaction capable:
read only:
identifier quote char:
non_nullable_columns:
owner usage: 3
max table name len: 12
max column name len: 12
need long data len:
max columns in table: 102
max columns in index: 1
max char literal len: 52428
max statement len: 52428
max row size: 52428

[3/31/2004 9:48:49 AM]ECOSYSTEM.: {call sp_MSgetversion
[3/31/2004 9:48:50 AM]ECOSYSTEM.: {call sp_helpdistpublisher (N'ECOSYSTEM')
[3/31/2004 9:48:50 AM]ECOSYSTEM.distribution: select datasource, srvid from master..sysservers where upper(srvname) = upper(N'ECOSYSTEM'
[3/31/2004 9:48:51 AM]ECOSYSTEM.distribution: {call sp_MSadd_merge_anonymous_agent(N'0', N'MLEcosystem', N'Production_MLEcosystem', N'MLEcosystem', N'ML-US',?, 1369)
[3/31/2004 9:48:53 AM]ECOSYSTEM.distribution: {call sp_MShelp_profile (10, 4, N'')
Percent Complete:
Connecting to Publisher 'ECOSYSTEM.MLEcosystem
Initializin
Percent Complete:
Connecting to Publisher 'ECOSYSTEM

Server: ECOSYSTE
DBMS: Microsoft SQL Serve
Version: 08.00.076
user name: db
API conformance:
SQL conformance:
transaction capable:
read only:
identifier quote char:
non_nullable_columns:
owner usage: 3
max table name len: 12
max column name len: 12
need long data len:
max columns in table: 102
max columns in index: 1
max char literal len: 52428
max statement len: 52428
max row size: 52428

[3/31/2004 9:48:58 AM]ECOSYSTEM.MLEcosystem: set nocount on declare @dbname sysname select @dbname = db_name() declare @collation nvarchar(255) select @collation = convert(nvarchar(255), databasepropertyex(@dbname, N'COLLATION')) select collationproperty(@collation, N'CODEPAGE') as 'CodePage', collationproperty(@collation, N'LCID') as 'LCID', collationproperty(@collation, N'COMPARISONSTYLE') as 'ComparisonStyle
The merge process could not initialize the Publisher 'ECOSYSTEM:MLEcosystem'
Percent Complete:
The merge process could not initialize the Publisher 'ECOSYSTEM:MLEcosystem'
Percent Complete:
Category:NUL
Source: Merge Replication Provide
Number: -214719871
Message: The merge process could not initialize the Publisher 'ECOSYSTEM:MLEcosystem'
Percent Complete:
Category:COMMAN
Source: Failed Comman
Number:
Message: {call master..sp_MScheck_agent_instance (N'9F48C1F5-1D8E-4886-864D-12230193FC4E', 4)
Percent Complete:
Category:SQLSERVE
Source: ECOSYSTE
Number: 2103
Message: Another merge agent for the subscription(s) is running
Disconnecting from Subscriber 'ML-US
Disconnecting from Publisher 'ECOSYSTEM
Disconnecting from Distributor 'ECOSYSTEM
Microsoft SQL Server Merge Agent 8.00.76
Copyright (c) 2000 Microsoft Corporatio
Microsoft SQL Server Replication Agent: ECOSYSTEM-MLEcosystem-Production_MLEcosystem-ML-US-MLEcosystem- 0

Percent Complete: 0
Connecting to Subscriber 'ML-US'
Connecting to Subscriber 'ML-US.MLEcosystem'

Server: ML-US
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288

[3/31/2004 9:50:01 AM]ML-US.MLEcosystem: {call sp_MSgetversion }
[3/31/2004 9:50:01 AM]ML-US.MLEcosystem: {?=call sp_helpsubscription_properties (N'ECOSYSTEM', N'MLEcosystem', N'Production_MLEcosystem')}
Distributor security mode: 0, login name: sa, password: ********.
[3/31/2004 9:50:01 AM]ML-US.MLEcosystem: {?=call sp_helpsubscription_properties (N'ECOSYSTEM', N'MLEcosystem', N'Production_MLEcosystem')}
Publisher security mode: 0, login name: sa, password: *********.
Percent Complete: 0
Connecting to Distributor 'ECOSYSTEM'
Connecting to Distributor 'ECOSYSTEM.'

Server: ECOSYSTEM
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288

[3/31/2004 9:50:04 AM]ECOSYSTEM.: {call sp_MSgetversion }
[3/31/2004 9:50:04 AM]ECOSYSTEM.: {call sp_helpdistpublisher (N'ECOSYSTEM') }
[3/31/2004 9:50:05 AM]ECOSYSTEM.distribution: select datasource, srvid from master..sysservers where upper(srvname) = upper(N'ECOSYSTEM')
[3/31/2004 9:50:06 AM]ECOSYSTEM.distribution: {call sp_MSadd_merge_anonymous_agent(N'0', N'MLEcosystem', N'Production_MLEcosystem', N'MLEcosystem', N'ML-US',?, 1369)}
[3/31/2004 9:50:06 AM]ECOSYSTEM.distribution: {call sp_MShelp_profile (10, 4, N'')}
Percent Complete: 0
Connecting to Publisher 'ECOSYSTEM.MLEcosystem'
Initializing
Percent Complete: 1
Connecting to Publisher 'ECOSYSTEM'

Server: ECOSYSTEM
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288

Hi Paul

Thank you for the message

I have been trying to sync these desperately for the last few hours. What I did was I was clicking on the Start Synchronizing at the subscriber in EM, and kept on querying my publisher database from the subscriber. At times my queries used to take around 30 seconds to return 5 rows (3 columns) while under normal circumstances it would take less than a second. And whenever this problem used to happen the sync used to give some error or the other

Below are some more entries from my log fil

_______________________________________________________________

Microsoft SQL Server Merge Agent 8.00.76
Copyright (c) 2000 Microsoft Corporatio
Microsoft SQL Server Replication Agent: ECOSYSTEM-MLEcosystem-Production_MLEcosystem-ML-US-MLEcosystem-

Percent Complete:
Connecting to Subscriber 'ML-US
Connecting to Subscriber 'ML-US.MLEcosystem

Server: ML-U
DBMS: Microsoft SQL Serve
Version: 08.00.076
user name: db
API conformance:
SQL conformance:
transaction capable:
read only:
identifier quote char:
non_nullable_columns:
owner usage: 3
max table name len: 12
max column name len: 12
need long data len:
max columns in table: 102
max columns in index: 1
max char literal len: 52428
max statement len: 52428
max row size: 52428

[3/31/2004 3:59:10 PM]ML-US.MLEcosystem: {call sp_MSgetversion
[3/31/2004 3:59:10 PM]ML-US.MLEcosystem: {?=call sp_helpsubscription_properties (N'ECOSYSTEM', N'MLEcosystem', N'Production_MLEcosystem')
Distributor security mode: 0, login name: sa, password: ********
[3/31/2004 3:59:10 PM]ML-US.MLEcosystem: {?=call sp_helpsubscription_properties (N'ECOSYSTEM', N'MLEcosystem', N'Production_MLEcosystem')
Publisher security mode: 0, login name: sa, password: *********
Percent Complete:
Connecting to Distributor 'ECOSYSTEM
Connecting to Distributor 'ECOSYSTEM.

Server: ECOSYSTE
DBMS: Microsoft SQL Serve
Version: 08.00.076
user name: db
API conformance:
SQL conformance:
transaction capable:
read only:
identifier quote char:
non_nullable_columns:
owner usage: 3
max table name len: 12
max column name len: 12
need long data len:
max columns in table: 102
max columns in index: 1
max char literal len: 52428
max statement len: 52428
max row size: 52428

[3/31/2004 3:59:14 PM]ECOSYSTEM.: {call sp_MSgetversion
[3/31/2004 3:59:14 PM]ECOSYSTEM.: {call sp_helpdistpublisher (N'ECOSYSTEM')
[3/31/2004 3:59:17 PM]ECOSYSTEM.distribution: select datasource, srvid from master..sysservers where upper(srvname) = upper(N'ECOSYSTEM'
[3/31/2004 3:59:17 PM]ECOSYSTEM.distribution: {call sp_MSadd_merge_anonymous_agent(N'0', N'MLEcosystem', N'Production_MLEcosystem', N'MLEcosystem', N'ML-US',?, 1369)
[3/31/2004 3:59:17 PM]ECOSYSTEM.distribution: {call sp_MShelp_profile (10, 4, N'')
Percent Complete:
Connecting to Publisher 'ECOSYSTEM.MLEcosystem
Initializin
Percent Complete:
Connecting to Publisher 'ECOSYSTEM

Server: ECOSYSTE
DBMS: Microsoft SQL Serve
Version: 08.00.076
user name: db
API conformance:
SQL conformance:
transaction capable:
read only:
identifier quote char:
non_nullable_columns:
owner usage: 3
max table name len: 12
max column name len: 12
need long data len:
max columns in table: 102
max columns in index: 1
max char literal len: 52428
max statement len: 52428
max row size: 52428

[3/31/2004 3:59:23 PM]ECOSYSTEM.MLEcosystem: set nocount on declare @dbname sysname select @dbname = db_name() declare @collation nvarchar(255) select @collation = convert(nvarchar(255), databasepropertyex(@dbname, N'COLLATION')) select collationproperty(@collation, N'CODEPAGE') as 'CodePage', collationproperty(@collation, N'LCID') as 'LCID', collationproperty(@collation, N'COMPARISONSTYLE') as 'ComparisonStyle
The merge process could not initialize the Publisher 'ECOSYSTEM:MLEcosystem'
Percent Complete:
The merge process could not initialize the Publisher 'ECOSYSTEM:MLEcosystem'
Percent Complete:
Category:NUL
Source: Merge Replication Provide
Number: -214719871
Message: The merge process could not initialize the Publisher 'ECOSYSTEM:MLEcosystem'
Percent Complete: 0
Category:COMMAND
Source: Failed Command
Number: 0
Message: {call master..sp_MScheck_agent_instance (N'9F48C1F5-1D8E-4886-864D-12230193FC4E', 4)}
Percent Complete: 0
Category:SQLSERVER
Source: ECOSYSTEM
Number: 21036
Message: Another merge agent for the subscription(s) is running.
Disconnecting from Subscriber 'ML-US'
Disconnecting from Publisher 'ECOSYSTEM'
Disconnecting from Distributor 'ECOSYSTEM'

-----------------------------------------------------------------------------------

I have specified the value for LoginTime to 3000 and QueryTimeOut to 3000. Also I have changed the DownloadGenerationsPerBatch, UploadGenerationsPerBatch and releated parameters to 5.

Do you think I should try a reinstall of SP3 on the subscriber?

Please advise. Thank you.

Regards,

Yuan's reply (below) makes sense to me. I mentioned ActiveX controls because this is one circumstance where an agent can be started programmatically, preventing your agent from working. If you can get hold of the erroring agent's process this could be killed like Yuan says. My suggestion is a bit of a sledgehammer and is one I have seen used before for this error, but in this circumstance it shouldn't be necessary to go this far, and I am not convinced that there is an actual corruption that is fixed, rather the reinstallation of the service pack simply removes problematic hung processes as a side-effect. From your explanation it sounds like this is a sporadic error, which is worrying. Anyway, I'd apply Yuan's reasoning and make sure that you don't have any merge agent being started programmatically form legacy code
Regards

Hi Paul

I am trying to implement Yuan's suggestion also.

But the thing is my servers synchronize based on synronichation scheduled via SQL Server. There is no possibility of any legacy code calling the merge agent

Also, I did try a reinstall of SP3 on my subscriber. It didn't solve the problem though

I am at present trying to implement Yuan's suggestion. I will post an update on the same later

I would be highly obliged if you have any other suggestion though

Thank you

Regards

Hi Paul, Yuan & Hillary

I just wanted to thank you all for helping me fix this problem

It was more of a bandwidth problem I guess. Once all the employees in my company left for the day, I tried to sync the servers, it synchronized without any problem

This problem occured as the synchronization didn't happen for nearly 18 hours and there was too much data to synchronize at once.

How do I make sure that this problem does not happen again?

Thank you all again

Regards

Manish
To be honest based on the error message I'm still not sure that this is the problem, but if it is you could try limiting the number of concurrent merge processes or schedule synchronization of the merge agents so as not to overlap (I'm assuming you have multiple subscribers)
HTH

which table did you repair? If it is one of the replication system tables
you will have probably have to drop your subscription and rebuild it.

I'd try a re-initialization first.
[quoted text, click to view]
the last 4 months. There was an error in one partiucular table at the
publisher because of which synchronization had stopped for around 14-18
hours between these two servers. I fixed this table using the DBCC
checktable with the REPAIR_DATA_LOSS option. No data as such was lost
though.
[quoted text, click to view]
____________________________________________________________________________
_____________
[quoted text, click to view]
____________________________________________________________________________
_____________
[quoted text, click to view]
Timeout parameter and quite a few parameters to solve this problem as these
two servers synchronize over the Internet.
[quoted text, click to view]

Manish,
the only time I've seen the error above was when the system was so corrupted
that I installed the latest sp once again then everything was OK. In the log
file there are a variety of errors including "Another merge agent for the
subscription(s) is running" which can be to do with activeX controls running
or hung processes but let's wait and see if anyone else has more specific
information on the main error, and if not then I'd apply the service pack.
Regards,
Paul Ibison

Hi Manish,

I learned when I synchronize the servers you got the following error:
"The merge process could not initialize the Publisher
'ECOSYSTEM:MLEcosystem'."

After reviewing the log file, I noticed the following error message.

[3/31/2004 9:48:58 AM]ECOSYSTEM.MLEcosystem: set nocount on declare @dbname
sysname select @dbname = db_name() declare @collation nvarchar(255) select
@collation = convert(nvarchar(255), databasepropertyex(@dbname,
N'COLLATION')) select collationproperty(@collation, N'CODEPAGE') as
'CodePage', collationproperty(@collation, N'LCID') as 'LCID',
collationproperty(@collation, N'COMPARISONSTYLE') as 'ComparisonStyle'
The merge process could not initialize the Publisher
'ECOSYSTEM:MLEcosystem'.
Percent Complete: 0
The merge process could not initialize the Publisher
'ECOSYSTEM:MLEcosystem'.
Percent Complete: 0
Category:NULL
Source: Merge Replication Provider
Number: -2147198719
Message: The merge process could not initialize the Publisher
'ECOSYSTEM:MLEcosystem'.
Percent Complete: 0
Category:COMMAND
Source: Failed Command
Number: 0
Message: {call master..sp_MScheck_agent_instance
(N'9F48C1F5-1D8E-4886-864D-12230193FC4E', 4)}
Percent Complete: 0
Category:SQLSERVER
Source: ECOSYSTEM
Number: 21036
Message: Another merge agent for the subscription(s) is running.
Disconnecting from Subscriber 'ML-US'
Disconnecting from Publisher 'ECOSYSTEM'
Disconnecting from Distributor 'ECOSYSTEM'

It is suspected that this problem is caused by the orphaned process. Please
try the following steps and check to see if it able to fix your problem.

1. In Enterprise Manager, under "Replication Monitor", Go to "Agents"
folder, Look for your failing Agent there. Right Click and Stop the agent
if it is in a retrying status.

2. Under Enterprise Manager, under "Replication Monitor", Go to "Agents"
folder, right click on the failing agent, choose Agent, properties. From
the Agent properties dialog box, copy the name of the agent.

3. In the Query Analyzer, modify Your_Agent_Name with the name from the
previous step in the following command and execute:
Select spid from sysprocesses where program_name = 'Your_Agent_Name'

4. Obtain the spid from the output, and use the Kill command to kill that
orphan process:
Kill spid

5. Restart the applicable agent.

What's the result?

Also, such issues tend to be complex and take up extensive research time.
I'd like to set your expectations that it may take a while for us to help
you narrow down the problem and we may eventually redirect you to PSS to
continue working with a dedicated Support Professional. If this is
critical, I'd recommend contacting PSS and opening a Support incident
troubleshoot this further. If you need any help in this regard, please let
me know.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.