Data Is Not Being Delivered to Subscribers

If it appears that data is not being delivered to Subscribers, there are two broad reasons:

• The data is not being applied due to filtering, an agent issue, or another replication error.
• The data is being deleted at the Subscriber after it has been applied.

Explanation

There are a number of possible causes for data not being delivered to Subscribers:

• The table is filtered, and there are no changes to deliver to a given Subscriber.
• One or more agents are not running or are failing with an error.
• A transactional subscription was initialized without a snapshot, and changes have occurred on the Publisher since the publication was created.
• Replication of stored procedure execution for a transactional publication produces different results at the Subscriber.
• The INSERT stored procedure used by a transactional article includes a condition that is not met.
• Data is deleted by a user, a replication script, or another application.
• Data is deleted by a trigger, or a trigger includes a ROLLBACK statement.

User Action

Before attempting to diagnose why data is not being delivered to Subscribers, we recommend that you use validation or the tablediff utility to verify that rows are missing:

• If the Distribution Agent or Merge Agent is able to run, determine whether data is missing by running binary checksum validation. You can also use row count validation, but this method does not reveal differences in the contents of the data. For more information, see Validating Replicated Data.
• If the Distribution Agent or Merge Agent cannot run, determine whether data is missing by running the tablediff utility. For information about using this utility on replicated tables, see How to: Compare Replicated Tables for Differences (Replication Programming).

Addressing the Cause of the Missing Data

The following actions address the causes listed in the "Explanation" section:

• The table is filtered, and there are no changes to deliver to a given Subscriber.
  It is possible that the rows missing at the Subscriber were not replicated because they do not meet the filtering criteria for the publication. All types of replication support static filters, and merge replication also supports parameterized filters and join filters. For more information, see Filtering Published Data. If one or more articles in the publication are filtered, execute the following procedures, and verify the value of the filter clause:
  • Static filter for snapshot and transactional publications: the filter_clause column returned by sp_helparticle (Transact-SQL).
  • Static filter or parameterized filter for merge publications: the subset_filterclause column returned by sp_helpmergearticle (Transact-SQL).
  • Join filter for merge publications: the join_filterclause column returned by sp_helpmergefilter (Transact-SQL).
    Use the filter clause to determine whether any of the missing rows meet the filtering criteria. For example, you could execute the filter clause against the table at the Publisher and determine whether the data returned matches the data at the Subscriber.
• One or more agents are not running or are failing with an error:
  • If you are initializing a subscription, ensure that the Snapshot Agent for the publication has completed before attempting to apply the snapshot with the Distribution Agent or the Merge Agent. If you attempt to apply the snapshot before it is completed, the following error occurs: "The initial snapshot for publication '%s' is not yet available."
  • For transactional replication, ensure that the Distribution Agent and Log Reader Agent are running; for merge replication, ensure the Merge Agent is running. For information about starting these agents, see How to: Start and Stop a Replication Agent (SQL Server Management Studio) and Programming Replication Agent Executables.
  • If an agent stops because of an error, view the error details for the agent to determine what the underlying cause is. For information about how to view error details for the Snapshot Agent and Log Reader Agent, see How to: View Information and Perform Tasks for the Agents Associated With a Publication (Replication Monitor). For information about the Distribution Agent and Merge Agent, see How to: View Information and Perform Tasks for the Agents Associated With a Subscription (Replication Monitor). If the error continues to occur, increase the logging of the agent and specify an output file for the log. Depending on the context of the error, this could provide the steps leading up to the error and/or additional error messages. For more information, see Replication Agents (Troubleshooting).
    Common errors that cause data not to be delivered include permissions issues and constraint violations. For more information about permissions issues, see Security Issues Are Preventing Data from Being Replicated. Constraint violations prevent rows from being inserted at the Subscriber.
    For transactional replication, constraint violations are treated as errors; by default they cause the Distribution Agent to stop synchronizing if they are encountered (for information about skipping these errors, see Skipping Errors in Transactional Replication). For merge replication, constraint violations are treated as conflicts; they are logged, but they do not cause the Merge Agent to stop synchronizing. For both types of replication, constraint violations can lead to non-convergence if an insert, update, or delete that succeeded at one node does not succeed at another.
    When a table is published, the default schema options specify that foreign key constraints and check constraints should be created in the subscription database with the NOT FOR REPLICATION option set. If your application requires different settings for constraints, change the schema options. For more information, see How to: Specify Schema Options (SQL Server Management Studio) and How to: Specify Schema Options (Replication Transact-SQL Programming).
• A transactional subscription was initialized without a snapshot, and changes have occurred on the Publisher since the publication was created:
  • If you enable a publication to be initialized from a backup, changes to published tables are tracked in the publication database log as soon as the publication is created. When a subscription is initialized, pending changes are delivered to the Subscriber as long as they are still available in the distribution database.
  • Unlike initializing from a backup, if you initialize a subscription using the replication support only option, you or your application must ensure that the data and schema are properly synchronized at the time you add the subscription. If, for example, there is activity on the Publisher between the time data and schema are copied to the Subscriber and the time at which the subscription is added, changes resulting from this activity might not be replicated to the Subscriber.
    For more information, see Initializing a Transactional Subscription Without a Snapshot.
• Replication of stored procedure execution for a transactional publication produces different results at the Subscriber.
  If you replicate the execution of a stored procedure, the procedure definition is replicated to the Subscriber when the subscription is initialized; when the procedure is executed at the Publisher, replication executes the corresponding procedure at the Subscriber. For more information, see Publishing Stored Procedure Execution in Transactional Replication.
  If the stored procedure performs a different action at the Subscriber or acts on different data than at the Publisher, non-convergence can occur. Consider a procedure that performs a calculation and then inserts data based on this calculation. If the Subscriber is filtered such that the calculation at the Subscriber is based on different data, the result inserted at the Subscriber could be different or the insert might not occur at all.
• The INSERT stored procedure used by a transactional article includes a condition that is not met.
  By default, transactional replication uses a set of stored procedures to propagate changes to Subscribers. You can also customize these procedures to include business logic required by your application. For more information, see Specifying How Changes Are Propagated for Transactional Articles. If the INSERT stored procedure includes a condition in its logic that is not met, the insert does not occur. Consider a procedure that is customized to check for a certain value in a table (Table A) at the Subscriber before allowing an insert into another table (Table B). If the value is not available in Table A because of an error or because data has not yet been replicated to this table, the expected row is missing from Table B.
• Data is being deleted by a user, a replication script, or another application:

  • If you want to allow users to delete data at the Subscriber, use merge replication, transactional replication with updatable subscriptions, or peer-to-peer transactional replication. Deletes are propagated to the Publisher, so the data at the Publisher and Subscriber eventually converge. For more information, see Merge Replication Overview and Publication Types for Transactional Replication.

  • If you want to prevent users from deleting data at the Subscriber, create a trigger for each table that contains the word ROLLBACK and uses the NOT FOR REPLICATION option (which prevents the trigger from firing when a replication agent performs an operation). For example:

    USE AdventureWorks
    GO
    CREATE TRIGGER prevent_user_dml
    ON Person.Address
    FOR INSERT, UPDATE, DELETE
    NOT FOR REPLICATION
    AS
    ROLLBACK
    

    For more information, see CREATE TRIGGER (Transact-SQL) and Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION.

  • Replication allows you to execute scripts before and after the snapshot is applied and during synchronization. The @pre_snapshot_script and @post_snapshot_script parameters of sp_addpublication and sp_addmergepublication allow you to specify scripts to run before and after the snapshot is applied. For more information, see Executing Scripts Before and After the Snapshot Is Applied. The stored procedure sp_addscriptexec allows you to execute a script during the synchronization process. For more information, see How to: Execute Scripts During Synchronization (Replication Transact-SQL Programming).
    These scripts are typically used for administrative tasks, such as adding logins at the Subscriber. If the scripts are used to delete data at a Subscriber that should be treated as read-only, the administrator must ensure that non-convergence does not result.

• Data is being deleted by a trigger, or a trigger includes a ROLLBACK statement.
  Triggers at the Subscriber must be managed properly so that they do not cause non-convergence or other issues:
  • Triggers should only cause data changes at a Subscriber if you use merge replication, transactional replication with updatable subscriptions, or peer-to-peer transactional replication. For more information, see Merge Replication Overview and Publication Types for Transactional Replication.
  • In many cases, triggers should use the NOT FOR REPLICATION option. If a trigger includes a ROLLBACK statement and the trigger does not use the NOT FOR REPLICATION option, rows that were replicated to a Subscriber might not be applied.
  • For transactional replication, there are additional considerations regarding the XACT_ABORT setting and using COMMIT and ROLLBACK statements in a trigger. For more information, see the "Triggers" section of Considerations for Transactional Replication.

See Also

Concepts

Troubleshooting Replication

Help and Information

Getting SQL Server 2005 Assistance