SQL Server Performance Forum – Threads Archive
SQL duplicates update commands in replication
I have 2 servers (2000) with transactional replication. The strange thing is that when I do UPDATE command on published table (just 1 record) on the publisher side in query analyzer – I get message "command completed successfully" 2 times (he does it twice) and on subscriber i get a transaction with 3 commands: update with old values, 2 updates with new values. So each UPDATE I get 3 times – that blows performance.INSERT, DELETE work as usual. Anyone has an idea why that happens? Thanks
I don’t do any transactional replication, so I am not familiar much with how it works. But to help diagnose what is going on, have you performed Profiler trace on what you described to see if it matches what you are seeing? This may or may not help much, but it is something I would do to gather more information on what is going on.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
I had similar behaviors under SQLServer 6.5. It was especially the UPDATE that were replicated in DELETE/INSERT commands. The answer of the Microsoft’s hot line was that it’s to optimize the space used by the rows of the tables including columns of VARCHAR type. Some dark things occur with the replication. Jean-Paul FOURNIAUD
Have you checked around SQL error logs and replication job steps for any information. Satya SKJ
That sounds about right to me with Transactional Replication (TR). The command completed successfully two times because you update your database once and then replication occurs instantly with TR and your subscriber is immediately updated. Therefore you see the process as occuring twice on the publisher. On the subscriber, think of it like an Update Trigger where the Old value and New Value are stored in the Updated table then the actual update made to the table. Hope that makes since. ———-
T Kelley
MS, MCDBA, OCA, CIW
I believe there was lot of enhancements and better design compared to SQL 6.5 replication and 2K’s one. 6.5 version was the beginning days for SQL Server, it learned a lot to face-off lacking utilities. Hope other agree in this regard.
]]>