Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Replication (2005)  Synchronization time with merge replication

Author  Topic  sami273 Starting Member 9 Posts Posted - 2007-05-02 : 14:07:48 Hello all! I have a problem with Sql server 2005 merge replication, when deleting big amounts of data from the publisher database. First, some basic facts about our configuration: 1) System has 150 subscribers in one merge publisher. 2) Publisher database size is about 10 gigabytes. Due to filtering, subscriber databases are sized from 0,5 --> 1 gigabytes each. 3) There is one table (named SALES), which has over 5 million rows in publisher database. 4) The SALES-table is filtered so, that each subscriber has about 300 thousand rows in this table 5) We use parameterized filters. Filtering clause uses hostname-parameter, and is quite simple. And the "issue": I delete 95000 rows (7000 rows per subscriber) from table SALES in publisher-database. System seems to work quite efficiently, all subscribers are synchronized in 5-10 minutes without any problems. I delete exactly 5 times more, 450000 rows (35000 rows per subscriber) from table SALES in publisher-database. This time the synchronization takes 9 hours! Synchronization status keeps displaying these errors: * The merge process was unable to create a new generation at the 'Publisher'. Troubleshoot by restarting the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200994) * Query timeout expired (Source: MSSQLServer, Error number: 0) * The merge process failed to execute a query because the query timed out. If this failure continues, increase the query timeout for the process. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQLServer, Error number: 0) I would have assumed, that if deleting of 95000 rows takes 5-10 minutes, deleting 5 times more should take 25-50 minutes. But apparently this is not the case. Any ideas why this is happening? How should we tune the system? Thanks in advance for your help!