Troubleshooting Materialization Failures

Troubleshoot subscriptions that have not materialized.

Prerequisites
Verify that data has failed to materialize by logging in to the replicate database using isql and executing a select command that selects the materialized columns from the replicate table.
Also make sure that all tasks required for subscription materialization have been completed. Subscription materialization may fail if you have not completed:

  • Creating replication definitions or function replication definitions

  • Marking tables or stored procedures for replication

  • Creating connections to the destination databases

  • Creating articles, if you are using them

  • Creating and validating publications, if you are using them

  • Marking text, unitext, or image columns for replication, if you are replicating text, unitext, and image columns.

  • Creating direct and indirect routes, if the destination database is connected to a Replication Server different from the primary database’s Replication Server

  • Creating logical connections, if you are using warm standby applications

Task
  1. If you are materializing a large amount of data, ensure that the num_threads and num_concurrent_subs parameters are large enough.
  2. Log in to the destination Replication Server and issue check subscription, which returns information that diagnoses the problem, including:

    • Other subscriptions to the same replication definition and replicate database have not yet processed

    • No connection to the primary Replication Server because of an incorrect login

    • Primary Replication Server down or out of stable queues

    • Stable Queue Manager (SQM), Stable Queue Transaction interface (SQT), and Distributor (DIST) threads down

    • Primary data server down, incorrect login, out of stable queues, or rows selected with holdlock

    • RepAgent problem

    • Route problem

    • Destination Replication Server—incorrect login or out of stable queues

    • Destination Replication Server Data Server Interface (DSI) problem—use admin who, dsi or admin who, sqm to determine what the specific problem is

    • Incorrect user privileges on destination database

  3. Log in to the primary Replication Server and also check for its subscription status using check subscription.
  4. Use rs_helppub and rs_helppubsub to find the publications and articles that a subscription is using.
  5. If some columns are not being materialized:
    1. Check replication status of text, unitext, and image columns.
    2. Verify that the replication definition is correctly defined.
    3. Verify that the publications and articles are correctly defined.
  6. Fix the problem.
  7. Run the replication system when you think you have solved the problem.

    If the subscription is still not materialized, analyze the error log again or complete any of the steps you have skipped.

Parent topic: Troubleshooting Overview