sql server replication: Hi!

I have one SQL 2005 SP2 Server in Paris and another one in Germany. The
locations are connected via VPN. The ping-time is less than 25ms, with a
bandwith of 2 MBit.

The database I want to replicate has 4 tables. Two of the tables replicate
without any errors, but the others one only replicate when they are very
small (less then 50 records).

On the local server in Paris the subscription works fine. Only the
subscription to the server in Germany doesn't work: The process could not
bulk copy into table "dbo"."..." (Error Message: Detect nonlogged agent
shutdown)

I can't understand the problem, because I use a packed snapshot which has a
size of 8 MB. I should be no problem to copy the file to the other server und
put the data into the tables.

I appreciate for any help!

Can you enable logging for your distribution agent for the German subscriber
and post the results back here?

http://support.microsoft.com/kb/312292

--
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
[quoted text, click to view]

The logfile (first part):

2007-07-04 14:34:13.530 Microsoft SQL Server Distribution Agent 9.00.3042.00
2007-07-04 14:34:13.530 Copyright (c) 2005 Microsoft Corporation
2007-07-04 14:34:13.530 Microsoft SQL Server Replication Agent: distrib
2007-07-04 14:34:13.530
2007-07-04 14:34:13.530 The timestamps prepended to the output lines are
expressed in terms of UTC time.
2007-07-04 14:34:13.530 User-specified agent parameter values:
-Subscriber HOISTDWH
-SubscriberDB DWHSync
-Publisher GETPAIDFR
-Distributor GETPAIDFR
-DistributorSecurityMode 1
-Publication DWHSync
-PublisherDB DWHSync
-Output D:\replicationLog.txt
-Outputverboselevel 2
-XJOBID 0x183A91C8CF68744C99BCF8BC985C0554
-XJOBNAME GETPAIDFR-DWHSync-DWHSync-HOISTDWH-21
-XSTEPID 2
-XSUBSYSTEM Distribution
-XSERVER GETPAIDFR
-XCMDLINE 0
-XCancelEventHandle 00000978
-XParentProcessHandle 0000047C
2007-07-04 14:34:13.545 Startup Delay: 4537 (msecs)
2007-07-04 14:34:18.097 Connecting to Distributor 'GETPAIDFR'
2007-07-04 14:34:18.097 Connecting to OLE DB Distributor at datasource:
'GETPAIDFR', location: '', catalog: '', providerstring: '' using provider
'SQLNCLI'
2007-07-04 14:34:18.159 OLE DB Distributor: GETPAIDFR
DBMS: Microsoft SQL Server
Version: 09.00.3042
catalog name:
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2007-07-04 14:34:18.159 OLE DB Distributor 'GETPAIDFR': exec
sp_helpdistpublisher N'GETPAIDFR'
2007-07-04 14:34:18.159 OLE DB Distributor 'GETPAIDFR': select @@SERVERNAME
2007-07-04 14:34:18.159 OLE DB Distributor: GETPAIDFR
DBMS: Microsoft SQL Server
Version: 09.00.3042
catalog name: distribution
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2007-07-04 14:34:18.159 OLE DB Distributor 'GETPAIDFR': execute
sp_server_info 18

2007-07-04 14:34:18.159 ANSI codepage: 1
OLE DB Distributor 'GETPAIDFR': select datasource, srvid from
master..sysservers where upper(srvname) = upper(N'GETPAIDFR')
2007-07-04 14:34:18.159 OLE DB Distributor 'GETPAIDFR': select datasource,
srvid from master..sysservers where upper(srvname) = upper(N'HOISTDWH')
2007-07-04 14:34:18.159 Subscriber security mode: 1, login name: .
2007-07-04 14:34:18.159 OLE DB Distributor 'GETPAIDFR': execute
sp_MShelp_profile 21, 3, N''
2007-07-04 14:34:18.159 Parameter values obtained from agent profile:
-bcpbatchsize 2147473647
-commitbatchsize 100
-commitbatchthreshold 1000
-historyverboselevel 1
-keepalivemessageinterval 300
-logintimeout 15
-maxbcpthreads 1
-maxdeliveredtransactions 0
-pollinginterval 5000
-querytimeout 1800
-skiperrors
-transactionsperhistory 100
2007-07-04 14:34:18.159 Connecting to Subscriber 'HOISTDWH'
2007-07-04 14:34:18.159 Connecting to OLE DB Subscriber at datasource:
'HOISTDWH', location: '', catalog: 'DWHSync', providerstring: '' using
provider 'SQLNCLI'
2007-07-04 14:34:18.424 OLE DB Subscriber: HOISTDWH
DBMS: Microsoft SQL Server
Version: 09.00.3042
catalog name: DWHSync
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2007-07-04 14:34:18.424 OLE DB Subscriber: HOISTDWH
DBMS: Microsoft SQL Server
Version: 09.00.3042
catalog name: DWHSync
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2007-07-04 14:34:18.455 OLE DB Subscriber 'HOISTDWH': execute sp_server_info
18

2007-07-04 14:34:18.486 ANSI codepage: 1
OLE DB Subscriber 'HOISTDWH': 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',cast(case when convert (int,databasepropertyex
(@dbname,'comparisonstyle')) & 0x1 = 0x1 then 0 else 1 end as bit) as
DB_CaseSensitive,cast(case when convert (int,serverproperty
('comparisonstyle')) & 0x1 = 0x1 then 0 else 1 end as bit) as
Server_CaseSensitive set nocount off
2007-07-04 14:34:18.548 OLE DB Distributor 'GETPAIDFR': {call
sys.sp_MSispeertopeeragent (?,?)}
2007-07-04 14:34:18.548 OLE DB Subscriber 'HOISTDWH': exec
sp_MSreplcheck_subscribe
2007-07-04 14:34:18.548 OLE DB Subscriber 'HOISTDWH': exec
sp_MScreate_sub_tables @tran_sub_table = 1, @property_table = 0, @p2p_table =
0
2007-07-04 14:34:18.642 OLE DB Subscriber 'HOISTDWH': if exists (select *
from sysindexes where id = object_id('MSreplication_subscriptions') and name
= 'uc1MSReplication_subscriptions') begin if not exists (select * from
sysindexes SI join sysindexkeys SIC on SI.id = SIC.id and SI.indid =
SIC.indid join syscolumns SC on SI.id = SC.id and SC.colid = SIC.colid
where SI.id = object_id('MSreplication_subscriptions') and SC.name =
'transaction_timestamp') begin drop index
MSreplication_subscriptions.uc1MSReplication_subscriptions CREATE UNIQUE
CLUSTERED INDEX uc1MSReplication_subscriptions ON
MSreplication_subscriptions(publication, publisher_db, publisher,
subscription_type, transaction_timestamp) end end
2007-07-04 14:34:18.658 OLE DB Subscriber 'HOISTDWH': if COLUMNPROPERTY(
OBJECT_ID('MSreplication_subscriptions'),'distribution_agent','AllowsNull')
<> 1 BEGIN alter table MSreplication_subscriptions alter column
distribution_agent sysname null end

logfile (second part):

2007-07-04 14:34:19.733 Extracting snapshot file 'GPACTV_4.bcp' from cabinet
file
2007-07-04 14:34:25.749 OLE DB Subscriber 'HOISTDWH': exec
sp_MSreset_synctran_bit @owner = N'dbo', @table = N'GPACTV'
2007-07-04 14:34:25.749 Extracted file 'GPACTV_4.bcp'
2007-07-04 14:34:26.279 Connecting to OLE DB Subscriber at datasource:
'HOISTDWH', location: '', catalog: 'DWHSync', providerstring: '' using
provider 'SQLNCLI'
2007-07-04 14:34:26.435 OLE DB Subscriber: HOISTDWH
DBMS: Microsoft SQL Server
Version: 09.00.3042
catalog name: DWHSync
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2007-07-04 14:34:26.528 Bulk copying data into table 'GPACTV'
2007-07-04 14:34:26.544 select * from "dbo"."GPACTV" where 1 = 2
2007-07-04 14:34:26.544 OLE DB Subscriber 'HOISTDWH': select * from
"dbo"."GPACTV" where 1 = 2

2007-07-04 14:36:30.513 Disconnecting from OLE DB Subscriber 'HOISTDWH'
2007-07-04 14:36:30.529 Agent message code 20037. The process could not bulk
copy into table '"dbo"."GPACTV"'.
2007-07-04 14:36:30.529 ErrorId = 6296, SourceTypeId = 2
ErrorCode = '20037'
ErrorText = 'The process could not bulk copy into table '"dbo"."GPACTV"'.'
2007-07-04 14:36:30.529 Adding alert to msdb..sysreplicationalerts: ErrorId
= 6296,
Transaction Seqno = 0000047a0000001000770000000f, Command ID = 26
Message: Replication-Replication Distribution Subsystem: agent
GETPAIDFR-DWHSync-DWHSync-HOISTDWH-21 failed. The process could not bulk copy
into table '"dbo"."GPACTV"'.ErrorId = 6296, SourceTypeId = 0
ErrorCode = ''
ErrorText = 'An existing connection was forcibly closed by the remote host.'
2007-07-04 14:36:30.529 Category:NULL
Source: Microsoft SQL Native Client
Number:
Message: An existing connection was forcibly closed by the remote host.
2007-07-04 14:36:30.529 ErrorId = 6296, SourceTypeId = 0
ErrorCode = ''
ErrorText = 'One or more BLOB columns could not be sent to the server,
attempt to recover from the problem failed.'
2007-07-04 14:36:30.529 Category:NULL
Source: Microsoft SQL Native Client
Number:
Message: One or more BLOB columns could not be sent to the server, attempt
to recover from the problem failed.
2007-07-04 14:36:30.529 ErrorId = 6296, SourceTypeId = 0
ErrorCode = '08S01'
ErrorText = 'Communication link failure'
2007-07-04 14:36:30.529 Category:NULL
Source: Microsoft SQL Native Client
Number: 08S01
Message: Communication link failure
2007-07-04 14:36:30.529 ErrorId = 6296, SourceTypeId = 0
ErrorCode = ''
ErrorText = 'Unspecified error'
2007-07-04 14:36:30.545 Category:NULL
Source: Microsoft SQL Native Client
Number:
Message: Unspecified error
2007-07-04 14:36:30.545 ErrorId = 6296, SourceTypeId = 0
ErrorCode = ''
ErrorText = 'Failed to send batch after max errors'
2007-07-04 14:36:30.545 Category:NULL
Source: Microsoft SQL Native Client
Number:
Message: Failed to send batch after max errors
2007-07-04 14:36:30.545 OLE DB Subscriber 'HOISTDWH': exec
dbo.sp_MSupdatelastsyncinfo N'GETPAIDFR',N'DWHSync', N'DWHSync', 0, 6, N'The
process could not bulk copy into table ''"dbo"."GPACTV"''.'
2007-07-04 14:36:30.591 Disconnecting from OLE DB Subscriber 'HOISTDWH'
2007-07-04 14:36:30.591 Disconnecting from OLE DB Subscriber 'HOISTDWH'
2007-07-04 14:36:30.591 Disconnecting from OLE DB Distributor 'GETPAIDFR'
2007-07-04 14:36:30.591 Disconnecting from OLE DB Distributor 'GETPAIDFR'



[quoted text, click to view]