Re: SQL2000 - Merge Replication - Timeout error

Hi Vini

Thanks for the script.

The re-indexing, along with setting the timeout to 43200 (12 hours) seems to
have done the trick.

Regards
Des Norton

"Vini" <Vini@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:86432F00-38D3-423D-B612-F92F3576F5E5@xxxxxxxxxxxxxxxx
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: [PHP] Re: Understanding persistent connections with oci8
    ... persistent connections per server and the timeout interval. ... may outside of php used. ... that's an over-simplification of the purpose of "persistent" ...
    (php.general)
  • Re: timout issues in cube processing SSAS 2005
    ... In BIDS double click on your data sourceand change parameter "Maximum Number of connections" to something smaller, ... All this will increase your processing time, buy you might avoid timeout. ... exec sp_configure @configname='remote query timeout ',@configvalue ... Analysis Services 2005 is on the same box as the SQL Server 2005 DB. ...
    (microsoft.public.sqlserver.olap)
  • Re: Async socket & active connections
    ... You could argue that blocking affects ... The "blocking = true" in the disconnect phase seems to solve the ... The second thing I noticed is that the cleanup procedure for 0 timeout ... 300K connections, enough to start worrying... ...
    (microsoft.public.dotnet.languages.csharp)
  • RE: [PHP] Re: Understanding persistent connections with oci8
    ... that's an over-simplification of the purpose of "persistent" ... The purpose of "timeout" is to tear down unused expensive connections ... if not -1 and infinite would be more along the line ...
    (php.general)
  • RE: Query Timeout
    ... I think u need to increase the timeout of merge agent..... ... Open the Replication monitor in that choose the Merge agent => Right-click ... => Click the New Profile ...
    (microsoft.public.sqlserver.replication)
Loading