Monday, November 12, 2012 9:29 PM
I have a transactional replication of a table on a sql server 2008 R2 server that recently is converting update commands into delete+insert command (deferred updates). The primary key is never updated. There is no unique index on the published table. The sql server portfolio on subscriptions show all rows that were updated on the published table are deleted and then inserted. I'm not sure if the deferred updates take place on publishing server or not.
- Edited by Bahram K Monday, November 12, 2012 9:53 PM
Tuesday, November 13, 2012 3:28 AMModerator
A deferred update should only be occurring for non-singleton updates to columns that are a part of a unique index or constraint. Deferred updates will also occur if trace flag 8202 is set on.
Please execute DBCC TRACESTATUS on the Publisher to check if trace flag 8202 is set on. If so, you can execute DBCC TRACEOFF (8202, -1) to disable trace flag 8202 which will disable deferred updates being sent to subscribers.
- Marked As Answer by Shulei ChenModerator Tuesday, November 20, 2012 9:27 AM
Tuesday, November 13, 2012 1:20 PMModeratorThey also occur if the update causes a change to a filtered condition. So if I am filtering on OrderDate and I change an OrderDate value to be below my filtering criteria, so the filter evaluates to false, the update will be decomposed as an delete followed by an insert.
looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
Tuesday, November 13, 2012 6:03 PMModerator