SQL Server Performance

Merge replication article number limitations

Discussion in 'SQL Server 2005 Replication' started by Lesleyg, Feb 20, 2008.

  1. Lesleyg New Member

    Hi all,
    I've upgraded a SQL 2000 SP3 merge replication publisher to SQL 2005. One publication on this server has over 700 articles and this replication has been running fine on SQL 2000.
    However, now that the server has been upgraded, the snapshot is failing with the following error:
    2008-02-20 13:02:28.13 spid54 Replication-Replication Snapshot Subsystem: agent SRV-GLAS029-ShipSure-ShipSureShipManagement-1 failed. The replication agent had encountered an exception.
    Source: Replication
    Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
    Exception Message: Too many table names in the query. The maximum allowable is 256
    Has the number of tables allowed in a merge replication publication changed? I can't find any documentation to say that it has...
    Thanks for any help.
  2. Lesleyg New Member

    After a little more research, I can answer my own question. Apparently there is a specified limit of 256 articles in a merge replication publication: http://msdn2.microsoft.com/en-us/library/ms143432.aspx
    This hasn't changed since SQL 2000, but there is a workaround suggested in this forum: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1059104&SiteID=17
    I truncated the msmerge_contents table as suggested & managed to successfully run the snapshot.
    I'm still concerned about this though. We've had this merge replication running on SQL 2000 for years with no problems in this area. We have a publication of 748 articles (18 of which are filtered) running to 12 subscribers. 256 seems like a very small limit compared to the number of articles allowed for transactional replication (32,767) and this workaround seems a bit flaky.
  3. dineshasanka Moderator

    asks you to
    Split the publication into multiple publications so that the combined number of published tables + Join Filters do not exceed 256 in one publication.

    Disable the dynamic filtering option in the merge publication by setting the @dynamic_filters parameter in the sp_addmergepublication stored procedure to FALSE.
  4. Lesleyg New Member

    Thanks Dinesh.
    I experienced performance problems in SQL 2000 when splitting the publication into multiple publications, so I'm not keen to do that again. And we need to use dynamic filters, so the second option is also a no-go.
    I'm surprised that this is an issue in SQL2005 as it appears that this fix was put into SQL2000 with SP2. I've raised a call with Microsoft to see if there is a 2005 hotfix.
  5. Lesleyg New Member

    Apparently this is an active bug and will be fixed in SP3. The only workaround suggested was to split up the publication into 3 separate publications. Not particularly happy about this esp as this issue was fixed in SQL2000 SP2.
    Guess I'll be waiting on the SP3 release before upgrading.
  6. dineshasanka Moderator

    have they decided to relase SP3. I was told that relesing SP3 is not yet confirmed
  7. satya Moderator

  8. Lesleyg New Member

    Thanks for that Satya. It doesn't appear to include the bug that we've come up against, but I'll apply it anyway.
    I was told that the specific bug limiting the number of merge replication articles to 256 would be fixed in SP3. But of course, they wouldn't commit to any timescale for the release of SP3.
  9. satya Moderator

    If possible can you get that name or contact who referred that it will be fixed in SP3 [:)]
  10. ponsee New Member

    I can confirm that It is not fixed on SQL 2005 SP3.
  11. satya Moderator

    Cool, interesting that you followed up this problem.
    If it is not fixed then better to raise a connect bug too from http://connect.microsoft.com link.

Share This Page