Re: SQL2000 - Merge Replication - Timeout error

Des,

The timeout is in your publisher and distributor server, not in the
subscriber.
Try run this little script in the database at the publisher:

DBCC DBREINDEX ('MSmerge_contents')
DBCC DBREINDEX ('MSmerge_genhistory')
DBCC DBREINDEX ('MSmerge_tombstone')

Then, update the database statistics.

Let me know if it works!

Best luck!


"Des Norton" wrote:

Hillary

Please bear with me. I am a developer that gets forced to wear the dreaded
DBA hat.

The agent profile properties do not appear to include packet size. How do I
set the packet size.

Using Profiler, what am I looking for, and how do I set it up


Reinitialising is an absolute LAST resort. Two of the tables have over 15
million records. With limited bandwidth, we are unable to initialise from
scratch. We have to clear out the offending tables and initialise. We then
insert and replicate 100000 records at a time. This takes about 8 days to
complete. We even tried replicating to another server on the same LAN, and
then attaching the replicated DB, but ALAS, the same old timeouts.


Regards
Des


"Hilary Cotter" <hilary.cotter@xxxxxxxxx> wrote in message
news:7626c695-3c15-4b07-87a2-3ff7a7406609@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Can you use the regular profile, a large timeout -3600 is probably
right, and then set packet size to the max (32767).

Run profiler on the subscriber to see what is going on? What is your
retention period? Is this the first time this subscriber has been
cleaned up?

In some cases like this out of frustration I have reinitialized the
subscription with upload to get through errors like this.
On Feb 1, 2:36 am, "Des Norton" <d...@xxxxxxxxxxxxxxxxx> wrote:
Hi NG

We have merge replication running successfully between 2 SQL2000 boxes via
VPN. The agent profile is "Slow Link". On Tuesday, the replication stopped
working. Verbose logging indicated a timeout issue. I created a new
profile with exactly the same parameters as the "Slow link" profile, and
upped the QueryTimeout from 300 seconds to 600 seconds. Still the same
errors.

I have continually increased this timeout, and it is now at 3600 seconds
(1
hour), and we still get the same errors.

Any help is greatly appreciated.
Des Norton

************************************************************************

[2/1/2008 4:00:13 AM]GENHS001.MFP_Collections: 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 'GENHS001.MFP_Collections'

Server: GENHS001
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

[2/1/2008 4:00:13 AM]GENHS001.MFP_Collections: {call sp_MSgetversion }
Percent Complete: 3
Retrieving publication information
Percent Complete: 4
Retrieving subscription information
Percent Complete: 4
The merge process is cleaning up meta data in database 'MFP_Collections'.
Percent Complete: 0
The process is running and is waiting for a response from one of the
backend
connections.
Percent Complete: 0
The process is running and is waiting for a response from one of the
backend
connections.
Percent Complete: 0
The process is running and is waiting for a response from one of the
backend
connections.
Percent Complete: 0
The process is running and is waiting for a response from one of the
backend
connections.
Percent Complete: 0
The process is running and is waiting for a response from one of the
backend
connections.
Percent Complete: 0
The process is running and is waiting for a response from one of the
backend
connections.
The merge process could not perform retention-based meta data cleanup in
database 'MFP_Collections'.
Percent Complete: 0
The merge process could not perform retention-based meta data cleanup in
database 'MFP_Collections'.
Percent Complete: 0
Category:NULL
Source: Merge Replication Provider
Number: -2147199467
Message: The merge process could not perform retention-based meta data
cleanup in database 'MFP_Collections'.
Percent Complete: 0
Category:COMMAND
Source: Failed Command
Number: 0
Message: {call sp_mergemetadataretentioncleanup(?, ?, ?)}
Percent Complete: 0
Category:SQLSERVER
Source: GENHS001
Number: 0
Message: The merge process timed out while executing a query. Reconfigure
the QueryTimeout parameter and retry the operation.
Disconnecting from Subscriber 'MFPAS1'
Disconnecting from Publisher 'GENHS001'
Disconnecting from Publisher 'GENHS001'
Disconnecting from Distributor 'GENHS001'
Microsoft SQL Server Merge Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent:
GENHS001-MFP_Collections-MFP_Collections-MFPAS1-MFP_Collections- 0

************************************************************************



.

Relevant Pages

  • Re: Sp_adjustpublisheridentityrange and resetting identity range
    ... > identity ranges you have set on your publisher. ... > ranges which will work for the lifetime of their replication solution. ... > should apply this value on the subscriber. ... >> restored the published database from a backup. ...
    (microsoft.public.sqlserver.replication)
  • Re: Couple of questions ...
    ... you would need to script out the creation of the distribution database first though and run it on your publisher. ... For push subscriptions you only need to run the subscriber setup script on the publisher, ... If one of them goes offline permanently you would need to restore the publication/subscriber database from back and reinitialize. ...
    (microsoft.public.sqlserver.replication)
  • Re: transactional replication problem
    ... If the distribution agen can't connect to the subscriber, ... Looking for a SQL Server replication book? ... > server B. Problem is with only one database. ...
    (microsoft.public.sqlserver.replication)
  • Re: Problems replicating over internet....Still....
    ... to your Publisher to download the snapshot and transactions. ... We can't test connectivity using ping, but you can ftp from the subscriber ... go to Client network utility. ... Server, ...
    (microsoft.public.sqlserver.replication)
  • Re: Need interface for displaying condition of subscriber DB to users
    ... This proc is run on your publisher and queries your subscriber. ... >> on your publisher issue the following in your master database ... >>> The users in the corporate office use the copies for reporting. ...
    (microsoft.public.sqlserver.replication)
Loading