Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
sql server 2008 R2 deferred updates replication

Answered sql server 2008 R2 deferred updates replication

  • Monday, November 12, 2012 9:29 PM
     
     
    Sign In to Vote
    0

    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.

    Thanks,

    Bahram


    • Edited by Bahram K Monday, November 12, 2012 9:53 PM
    •  
     

All Replies

  • Tuesday, November 13, 2012 3:28 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hi Bahram,

    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.

    Brandon Williams (blog | linkedin)

     
  • Tuesday, November 13, 2012 1:20 PM
    Moderator
     
     
    Sign In to Vote
    0
    They 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 PM
    Moderator
     
     
    Sign In to Vote
    0
    I don't think this is a filtering condition.  An update that causes a row to meet or not meet a filtering condition would be decomposed as either an insert, or a delete, not both.

    Brandon Williams (blog | linkedin)