none
Merge Replication on SQL Server 2008 R2 hangs at "Downloading data changes to the Subscriber" RRS feed

  • Question

  • All was working fine and suddenly this morning, all pubs related to 1 database stopped working. i.e., hang by the message "Downloading data changes to the Subscriber". It first uploads the changes fine and then at this message hangs and doesn't move from there and eventually fails with the message "String or binary data would be truncated". Very odd.

    regards

    Shiva

    Thursday, August 30, 2012 12:43 PM

Answers

  • Hi All,

    Solved the problem myself by decreasing the batch size from 100000 to 10000 and it started working again.

    regards

    Shiva

    • Marked as answer by Shiva Kumar Wednesday, September 5, 2012 7:30 AM
    Wednesday, September 5, 2012 7:30 AM

All replies

  • What version of SQL Server are you using?

    Can you try enabling verbose log as per this article http://support.microsoft.com/kb/312292 and post the output?


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Thursday, August 30, 2012 2:27 PM
  • Using SQL Server 2008 R2 Std Edition 64bit (replicating to MSDE 2000 SP4)

    Verbose output below:

    2012-08-30 15:48:01.308 Microsoft SQL Server Merge Agent 10.50.2500.0
    2012-08-30 15:48:01.496 Copyright (c) 2008 Microsoft Corporation
    2012-08-30 15:48:01.605 Microsoft SQL Server Replication Agent: replmerg
    2012-08-30 15:48:01.714 
    2012-08-30 15:48:01.776 The timestamps prepended to the output lines are expressed in terms of UTC time.
    2012-08-30 15:48:01.839 User-specified agent parameter values:
    -Publisher CYCLEDB2
    -PublisherDB LHOTSEREPL
    -Publication CYCLEREPL_Workshop2
    -Subscriber 25-SERVER\CS
    -SubscriberDB Stores
    -Distributor CYCLEDB2
    -DistributorSecurityMode 1
    -Output C:\25Serv_Workshop.txt
    -OutputVerboseLevel 2
    -UploadGenerationsPerBatch 10
    -DownloadGenerationsPerBatch 20
    -XJOBID 0x43367FD4894DCE47BE1D17988353056A
    -XJOBNAME CYCLEDB2-LHOTSEREPL-CYCLEREPL_Workshop2-25-SERVER\CS-330
    -XSTEPID 2
    -XSUBSYSTEM Merge
    -XSERVER CYCLEDB2
    -XCMDLINE 0
    -XCancelEventHandle 00000000000025C4
    -XParentProcessHandle 00000000000036FC
    2012-08-30 15:48:02.666 Percent Complete: 0
    2012-08-30 15:48:02.728 Connecting to Distributor 'CYCLEDB2'
    2012-08-30 15:48:02.837 Connecting to OLE DB Distributor at datasource: 'CYCLEDB2', location: '', catalog: '', providerstring: '' using provider 'SQLNCLI10'
    2012-08-30 15:48:02.931 OLE DB Distributor: CYCLEDB2
    DBMS: Microsoft SQL Server
    Version: 10.50.2500
    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
    2012-08-30 15:48:02.993 OLE DB Distributor 'CYCLEDB2': {call sp_MSgetversion }
    2012-08-30 15:48:03.071 OLE DB Distributor 'CYCLEDB2': {call sp_helpdistpublisher (N'CYCLEDB2') }
    2012-08-30 15:48:03.149 OLE DB Distributor 'CYCLEDB2': {call sp_MShelp_repl_agent (N'CYCLEDB2', N'LHOTSEREPL', N'CYCLEREPL_Workshop2', N'25-SERVER\CS', N'Stores', 1)}
    2012-08-30 15:48:03.243 OLE DB Distributor 'CYCLEDB2': select datasource, srvid from master..sysservers where upper(srvname) = upper(N'CYCLEDB2')
    2012-08-30 15:48:03.336 OLE DB Distributor 'CYCLEDB2': {call sp_MShelp_merge_agentid (0,N'LHOTSEREPL',N'CYCLEREPL_Workshop2',null,N'Stores',90,N'25-SERVER\CS')}
    2012-08-30 15:48:03.414 OLE DB Distributor 'CYCLEDB2': {call sp_MShelp_profile (330, 4, N'')}
    2012-08-30 15:48:03.492 Percent Complete: 0
    2012-08-30 15:48:03.492 Connecting to OLE DB Publisher at datasource: 'CYCLEDB2', location: '', catalog: 'LHOTSEREPL', providerstring: '' using provider 'SQLNCLI10'
    2012-08-30 15:48:03.570 Initializing
    2012-08-30 15:48:03.648 OLE DB Publisher: CYCLEDB2
    DBMS: Microsoft SQL Server
    Version: 10.50.2500
    catalog name: LHOTSEREPL
    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
    2012-08-30 15:48:03.742 OLE DB Distributor 'CYCLEDB2': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    2012-08-30 15:48:03.882 OLE DB Publisher 'CYCLEDB2': 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
    2012-08-30 15:48:04.023 Percent Complete: 0
    2012-08-30 15:48:04.116 OLE DB Publisher 'CYCLEDB2': {call sp_MSgetversion }
    2012-08-30 15:48:04.226 Connecting to Publisher 'CYCLEDB2'
    2012-08-30 15:48:04.397 Connecting to OLE DB Publisher at datasource: 'CYCLEDB2', location: '', catalog: 'LHOTSEREPL', providerstring: '' using provider 'SQLNCLI10'
    2012-08-30 15:48:04.538 OLE DB Distributor 'CYCLEDB2': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    2012-08-30 15:48:04.678 OLE DB Publisher: CYCLEDB2
    DBMS: Microsoft SQL Server
    Version: 10.50.2500
    catalog name: LHOTSEREPL
    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
    2012-08-30 15:48:04.834 OLE DB Distributor 'CYCLEDB2': {call sp_MShelp_repl_agent (N'CYCLEDB2', N'LHOTSEREPL', N'CYCLEREPL_Workshop2', N'25-SERVER\CS', N'Stores', 1)}
    2012-08-30 15:48:04.928 Connecting to OLE DB Subscriber at datasource: '25-SERVER\CS', location: '', catalog: 'Stores', providerstring: '' using provider 'SQLNCLI10'
    2012-08-30 15:48:05.271 OLE DB Subscriber: 25-SERVER\CS
    DBMS: Microsoft SQL Server
    Version: 08.00.2039
    catalog name: Stores
    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
    2012-08-30 15:48:05.552 OLE DB Subscriber '25-SERVER\CS': {call sp_MSgetversion }
    2012-08-30 15:48:05.770 OLE DB Subscriber '25-SERVER\CS': 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
    2012-08-30 15:48:06.612 Percent Complete: 0
    2012-08-30 15:48:06.690 Connecting to Subscriber '25-SERVER\CS'
    2012-08-30 15:48:06.768 OLE DB Distributor 'CYCLEDB2': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    2012-08-30 15:48:06.878 Percent Complete: 0
    2012-08-30 15:48:06.924 Retrieving publication information
    2012-08-30 15:48:06.971 OLE DB Distributor 'CYCLEDB2': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    2012-08-30 15:48:07.049 Percent Complete: 0
    2012-08-30 15:48:07.112 Retrieving subscription information.
    2012-08-30 15:48:07.174 OLE DB Distributor 'CYCLEDB2': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    2012-08-30 15:48:13.258 Percent Complete: 0
    2012-08-30 15:48:13.320 Uploading data changes to the Publisher
    2012-08-30 15:48:13.398 OLE DB Distributor 'CYCLEDB2': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    2012-08-30 15:48:13.554 Connecting to OLE DB Subscriber at datasource: '25-SERVER\CS', location: '', catalog: 'Stores', providerstring: '' using provider 'SQLNCLI10'
    2012-08-30 15:48:13.804 OLE DB Subscriber: 25-SERVER\CS
    DBMS: Microsoft SQL Server
    Version: 08.00.2039
    catalog name: Stores
    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
    2012-08-30 15:48:14.054 Connecting to OLE DB Publisher at datasource: 'CYCLEDB2', location: '', catalog: 'LHOTSEREPL', providerstring: '' using provider 'SQLNCLI10'
    2012-08-30 15:48:14.116 OLE DB Publisher: CYCLEDB2
    DBMS: Microsoft SQL Server
    Version: 10.50.2500
    catalog name: LHOTSEREPL
    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
    2012-08-30 15:48:14.178 Connecting to OLE DB Subscriber at datasource: '25-SERVER\CS', location: '', catalog: 'Stores', providerstring: '' using provider 'SQLNCLI10'
    2012-08-30 15:48:14.490 OLE DB Subscriber: 25-SERVER\CS
    DBMS: Microsoft SQL Server
    Version: 08.00.2039
    catalog name: Stores
    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
    2012-08-30 15:48:14.802 Connecting to OLE DB Publisher at datasource: 'CYCLEDB2', location: '', catalog: 'LHOTSEREPL', providerstring: '' using provider 'SQLNCLI10'
    2012-08-30 15:48:14.865 OLE DB Publisher: CYCLEDB2
    DBMS: Microsoft SQL Server
    Version: 10.50.2500
    catalog name: LHOTSEREPL
    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
    2012-08-30 15:48:14.927 Percent Complete: 0
    2012-08-30 15:48:15.005 Enumerating deletes in all articles
    2012-08-30 15:48:15.114 OLE DB Distributor 'CYCLEDB2': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    2012-08-30 15:48:15.255 Percent Complete: 0
    2012-08-30 15:48:15.333 Enumerating inserts and updates in article 'WorkShopDetails'
    2012-08-30 15:48:15.426 OLE DB Distributor 'CYCLEDB2': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    2012-08-30 15:48:15.504 Percent Complete: 0
    2012-08-30 15:48:15.582 Enumerating inserts and updates in article 'WorkShopHeader'
    2012-08-30 15:48:15.660 OLE DB Distributor 'CYCLEDB2': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    2012-08-30 15:48:15.738 Percent Complete: 0
    2012-08-30 15:48:15.816 Uploaded 13 change(s) in 'WorkShopDetails' (8 inserts, 1 update, 4 deletes): 13 total
    2012-08-30 15:48:15.910 OLE DB Distributor 'CYCLEDB2': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    2012-08-30 15:48:16.035 Percent Complete: 0
    2012-08-30 15:48:16.113 Uploaded 3 change(s) in 'WorkShopHeader' (1 insert, 2 updates): 3 total
    2012-08-30 15:48:16.175 OLE DB Distributor 'CYCLEDB2': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    2012-08-30 15:48:16.316 Percent Complete: 0
    2012-08-30 15:48:16.378 Downloading data changes to the Subscriber
    2012-08-30 15:48:16.440 OLE DB Distributor 'CYCLEDB2': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}

    Thursday, August 30, 2012 4:28 PM
  • Hi Shiva Kumar,

    Regarding to the error message and your description, the merge replication process is unable to create a new generation at the 'Subscriber'.

    Please try "update sysmergepublications set [generation_leveling_threshold] = 0" on both Subscriber and Publisher, if this still does not work, then try to run this command to query "select count (*) from dbo.MSmerge_contents" and "select count (*) from dbo.MSmerge_genhistory" on the Subscriber.

    If both query indefinely with no results, instanly there is some Locking problem, so you can try to restarted the Subscriber Database Engine and started the Agent.

     


    Regards, Amber zhang

    Friday, August 31, 2012 7:37 AM
    Moderator
  • Hi All,

    Solved the problem myself by decreasing the batch size from 100000 to 10000 and it started working again.

    regards

    Shiva

    • Marked as answer by Shiva Kumar Wednesday, September 5, 2012 7:30 AM
    Wednesday, September 5, 2012 7:30 AM