Merge heavy on distr cpu

125Gb heavy OLTP publication db w/ Transactional Replication replicating 400
tables to 5 different servers. No problems on remote distributor, cpu hardly
used, sits practically idle most of the day staying under 10%. Ram is used
however not getting taxed.
We added merge replication for 1 table and the cpu's pegged to 90 to 100%
each time the merge agents pick up transactions at pub. Every 20 minutes a
job populated this table w/ 100 to 200 inserts at publisher, the 5 merge
agents kick in immediately (continuously running) and spike cpu bringing
server to a crawl.
Publisher, Distributor, & all Subscribers on LAN w/ 100Mbps connections.
SQL 2000 SP3 on all servers. Distributor is a 6CPU PIII 700Mhz each. New
Distributor hardware not an option. Have to limp along until new budgets kick
in...
Thoughts...?
TIA,
Chris
Chris,
are the 100/200 inserts involving BLOB datatypes? How long does the spike
last? Anyway, I'd change the merge agents from running continuously, in
order to stagger their impact on the publisher.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Merge agent error

We have set up a merge publication which has about 34 subscribers. Suddenly
when i try to add a new subscriber, everything works fine until i start the
merge agent on the subscriber.
I get the error : Publication 'xxx' does not exist
Invalid cursor position.
I have included a part of the agent logging (verboselogging 2)
Start logging --
Microsoft SQL Server Merge Agent 8.00.382
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: SQLSRV-CRM-BOOSTmerge-BNM-CRM- 0
Percent Complete: 0
Connecting to Subscriber 'BNM'
Connecting to Subscriber 'BNM.CRM'
Server: BNM
DBMS: Microsoft SQL Server
Version: 08.00.0384
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
[4-3-2005 14:03:42]BNM.CRM: {call sp_MSgetversion }
[4-3-2005 14:03:42]BNM.CRM: {?=call sp_helpsubscription_properties
(N'SQLSRV', N'CRM', N'BOOSTmerge')}
Distributor security mode: 0, login name: BNM, password: ********.
[4-3-2005 14:03:42]BNM.CRM: {?=call sp_helpsubscription_properties
(N'SQLSRV', N'CRM', N'BOOSTmerge')}
Publisher security mode: 0, login name: BNM, password: bn159.
Percent Complete: 0
Connecting to Distributor 'SQLSRV'
Connecting to Distributor 'SQLSRV.'
Server: SQLSRV
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: BNM
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
[4-3-2005 14:03:42]SQLSRV.: {call sp_MSgetversion }
[4-3-2005 14:03:42]SQLSRV.: {call sp_helpdistpublisher (N'SQLSRV') }
[4-3-2005 14:03:42]SQLSRV.distribution: select datasource, srvid from
master..sysservers where upper(srvname) = upper(N'SQLSRV')
[4-3-2005 14:03:42]SQLSRV.distribution: select datasource, srvid from
master..sysservers where upper(srvname) = upper(N'BNM')
[4-3-2005 14:03:42]SQLSRV.distribution: {call sp_MShelp_merge_agentid (0,
N'CRM', N'BOOSTmerge', 33, N'CRM')}
[4-3-2005 14:03:42]SQLSRV.distribution: {call sp_MShelp_profile (81, 4, N'')}
Percent Complete: 0
Connecting to Publisher 'SQLSRV.CRM'
Initializing
Server: SQLSRV
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: BNM
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
[4-3-2005 14:03:42]SQLSRV.CRM: 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'
Connecting to Publisher 'SQLSRV.CRM'
Server: SQLSRV
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: BNM
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
[4-3-2005 14:03:42]SQLSRV.CRM: {call sp_MSgetversion }
Publication 'BOOSTmerge' does not exist.
Percent Complete: 0
Publication 'BOOSTmerge' does not exist.
Percent Complete: 1
Connecting to Publisher 'SQLSRV'
Percent Complete: 0
Category:NULL
Source: Merge Replication Provider
Number: -2147201022
Message: Publication 'BOOSTmerge' does not exist.
Percent Complete: 0
Category:SQLSERVER
Source: SQLSRV
Number: 0
Message: Invalid cursor state
Disconnecting from Subscriber 'BNM'
Disconnecting from Publisher 'SQLSRV'
Disconnecting from Publisher 'SQLSRV'
Disconnecting from Distributor 'SQLSRV'
On the server SQLSRV can you run sp_helppublication to ensure that
BOOSTmerge exists.
Rgds,
Paul Ibison
"Nol Thoelen" <NolThoelen@.discussions.microsoft.com> wrote in message
news:801D793F-9162-434D-8DA9-620C0C1A48F0@.microsoft.com...
> We have set up a merge publication which has about 34 subscribers.
> Suddenly
> when i try to add a new subscriber, everything works fine until i start
> the
> merge agent on the subscriber.
> I get the error : Publication 'xxx' does not exist
> Invalid cursor position.
> I have included a part of the agent logging (verboselogging 2)
> Start logging --
> Microsoft SQL Server Merge Agent 8.00.382
> Copyright (c) 2000 Microsoft Corporation
> Microsoft SQL Server Replication Agent: SQLSRV-CRM-BOOSTmerge-BNM-CRM- 0
> Percent Complete: 0
> Connecting to Subscriber 'BNM'
> Connecting to Subscriber 'BNM.CRM'
> Server: BNM
> DBMS: Microsoft SQL Server
> Version: 08.00.0384
> 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
> [4-3-2005 14:03:42]BNM.CRM: {call sp_MSgetversion }
> [4-3-2005 14:03:42]BNM.CRM: {?=call sp_helpsubscription_properties
> (N'SQLSRV', N'CRM', N'BOOSTmerge')}
> Distributor security mode: 0, login name: BNM, password: ********.
> [4-3-2005 14:03:42]BNM.CRM: {?=call sp_helpsubscription_properties
> (N'SQLSRV', N'CRM', N'BOOSTmerge')}
> Publisher security mode: 0, login name: BNM, password: bn159.
> Percent Complete: 0
> Connecting to Distributor 'SQLSRV'
> Connecting to Distributor 'SQLSRV.'
> Server: SQLSRV
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: BNM
> 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
> [4-3-2005 14:03:42]SQLSRV.: {call sp_MSgetversion }
> [4-3-2005 14:03:42]SQLSRV.: {call sp_helpdistpublisher (N'SQLSRV') }
> [4-3-2005 14:03:42]SQLSRV.distribution: select datasource, srvid from
> master..sysservers where upper(srvname) = upper(N'SQLSRV')
> [4-3-2005 14:03:42]SQLSRV.distribution: select datasource, srvid from
> master..sysservers where upper(srvname) = upper(N'BNM')
> [4-3-2005 14:03:42]SQLSRV.distribution: {call sp_MShelp_merge_agentid (0,
> N'CRM', N'BOOSTmerge', 33, N'CRM')}
> [4-3-2005 14:03:42]SQLSRV.distribution: {call sp_MShelp_profile (81, 4,
> N'')}
> Percent Complete: 0
> Connecting to Publisher 'SQLSRV.CRM'
> Initializing
> Server: SQLSRV
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: BNM
> 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
> [4-3-2005 14:03:42]SQLSRV.CRM: 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'
> Connecting to Publisher 'SQLSRV.CRM'
> Server: SQLSRV
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: BNM
> 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
> [4-3-2005 14:03:42]SQLSRV.CRM: {call sp_MSgetversion }
> Publication 'BOOSTmerge' does not exist.
> Percent Complete: 0
> Publication 'BOOSTmerge' does not exist.
> Percent Complete: 1
> Connecting to Publisher 'SQLSRV'
> Percent Complete: 0
> Category:NULL
> Source: Merge Replication Provider
> Number: -2147201022
> Message: Publication 'BOOSTmerge' does not exist.
> Percent Complete: 0
> Category:SQLSERVER
> Source: SQLSRV
> Number: 0
> Message: Invalid cursor state
> Disconnecting from Subscriber 'BNM'
> Disconnecting from Publisher 'SQLSRV'
> Disconnecting from Publisher 'SQLSRV'
> Disconnecting from Distributor 'SQLSRV'
>
|||I have tried this procedure and it runs correctly. My opinion is that it has
something todo with data which is not cleaned up correctly.
I had the same subscriber running before on the same publication, but the
machine needed to be re?nstalled after a disc crash.
When i try to subscribe now, i get this error.
Another weird thing is that adding the subscribtion works fine, but in the
list of all the subscribers, the subscription has status deactivated. Even
after running the snapshot agent.
"Paul Ibison" wrote:

> On the server SQLSRV can you run sp_helppublication to ensure that
> BOOSTmerge exists.
> Rgds,
> Paul Ibison
> "No?l Thoelen" <NolThoelen@.discussions.microsoft.com> wrote in message
> news:801D793F-9162-434D-8DA9-620C0C1A48F0@.microsoft.com...
>
>
|||I can't tell what happened when the disk crashed and the
machine was rebuilt. Somehow the subscriber details on
the publisher seem to refer to the old subscription, and
have been deactivated presumably because of not
synchronizing. I'd drop the subscription/subscriber, make
sure that the builds are done to the correct service pack
level, perhaps even reapply sp3a on the subscriber if
there's any doubt, then add the subscriber/subscription
from scratch.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql