Michael Ellerbeck

Log Reader Agent Recovery
The simplest way to recover the Log Reader Agent is to run the following command against the published database:

• sp_replrestart

This effectively tells SQL to restart replication NOW, thus ignoring all transactions that have occurred between the time of the failure and the time you run the command.  The longer you wait to run this command, the more activity in the database that gets ignored, which likely results in more tables that fall out of sync.

Now that the Log Reader Agent is capturing transactions for replication, the Distribution Agent will likely get upset because there are transactions missing.  I specifically received the following error:

• The row was not found at the Subscriber when applying the replicated command

This error causes the Distribution Agent to fail, but there is a system profile for the Distribution Agent that you can select to bypass the data consistency errors.

• Launch Replication Monitor
• In the left-hand column
  • Expand the DB server that contains the published database
  • Select the Publication
• In the right-hand pane
  • Double-click the Subscription
• In the Subscription window
  • Go to the Action menu and select Agent Profile
  • Select the profile: Continue on data consistency errors. and click OK
    • Be sure to note which profile was selected before changing it so that you can select the appropriate option once recovery is complete
• If the Distribution Agent is currently running (it’s likely in a fail/retry loop), you’ll need to:
  • Go to the Action menu and select Stop Distribution Agent
  • Go to the Action menu and select Start Distribution Agent
• If there is more than one subscription, repeat these steps for any additional subscriptions

• Launch Replication Monitor
• In the left-hand column of Replication Monitor
  • Expand the DB server that contains the published database
  • Right-click the Publication and select Validate Subscriptions…
  • Verify Validate all SQL Server Subscriptions is selected
  • Click the Validation Options… button and verify the validation options – I recommend selecting the following options:
    • Compute a fast row count: if differences are found, compute an actual row count
    • Compare checksums to verify row data (this process can take a long time)
  • Once you are satisfied with the validation options, click OK and then click OK to actually queue up the validation process
    • Please note: for large databases, this process may take a while (and the Validate Subscriptions window may appear asNot Responding)

• In the right-hand pane of Replication Monitor
  • Double-click the Subscription
• In the Subscription window:
  • Go to the Action menu and select Auto Refresh

The Comments column will contain the following message if a table is out of sync:

• Table ‘MyTable’ might be out of synchronization.  Rowcounts (actual: %value, expected: %value).  Checksum values  (actual: -%value, expected: -%value).

Make a list of all tables that are returned by the aforementioned script.

Now the determination needs to be made as to the level of impact.

• The Reinitialize All Subscriptions option should be used if the following is true:
  • Large number of tables affected (majority of published tables)
  • Unaffected tables are small in size (if the snapshot for the unaffected tables is going to be very small, it’s much easier to just reinitialize everything)
• Dropping and re-adding individual tables should be used if the following is true:
  • The number of tables affected is far less than the total number of tables
  • The tables that are unaffected are very large in size and will cause significant latency when pushing the snapshot

Reinitialize All Subscriptions
Follow this process if the determination has been made to use the Reinitialize All Subscriptionsoption:

• In the left-hand column of Replication Monitor
  • Expand the DB server that contains the published database
  • Right-click the Publication and select Reinitialize All Subscriptions…
  • Verify Use a new snapshot is selected
  • Verify Generate the new snapshot now is NOT selected
  • Click the Mark For Reinitialization button
    • Please note: for large databases, this process may take a while (and the Replication Monitor window may appear as Not Responding)
• In the right-hand pane of Replication Monitor
  • Select the Agents tab (in SQL 2005 select the Warnings and Agents tab)
  • Right click the Snapshot Agent and select Start Agent
    • The reason for performing this manually is that sometimes when you select the Generate the new snapshot now option, it kicks off the Snapshot Agent before the reinitialization is complete which causes blocking, deadlocks and major performance issues.

Recover out of sync tables
If the determination has been made to recover the individual tables, use the list of tables generated from the validation process and follow this process:

• In the left-hand column of Replication Monitor
  • Expand the DB server that contains the published database
  • Right-click the Publication and select Properties
  • Select the Articles page in the left-hand column
  • Once the center page has populated, expand each table published to determine if the table is filtered (i.e. not all columns in the table are published).
    • If tables are filtered, make a note of the columns that are not pushed for each table
  • Once review of the tables is complete, click Cancel
    • If you click OK after expanding tables, it will invalidate the entire snapshot and you will end up reinitializing all articles in the publication
  • Right-click the Publication and select Properties
  • Select the Articles page in the left-hand column
  • Clear the check boxes for all out of sync tables and click OK
  • Right-click the Publication and select Properties
  • Select the Articles page in the left-hand column
  • Select the affected tables in the center pane
    • If any tables were not completely replicated, be sure to reference your notes regarding which columns are replicated
  • Click OK when table selection is complete
    • Note: If you receive an error that the entire snapshot will be invalidated, close the Publication Properties window and try adding in a few tables at a time until all tables are selected.
  • In the right-hand pane of Replication Monitor
    • Select the Agents tab (in SQL 2005 select the Warnings and Agents tab)
    • Right click the Snapshot Agent and select Start Agent
  • Double-click the Subscription
  • Go to the Action menu and select Auto Refresh

• In the left-hand column of Replication Monitor
  • Expand the DB server that contains the published database
  • Select the Publication
• In the right-hand pane of Replication Monitor
  • Double-click the Subscription
• In the Subscription window
  • Go to the Action menu and select Agent Profile
  • Select the profile that was configured before you changed it (if unsure, the Default agent profile is typically the default) and click OK
• If there is more than one subscription, repeat these steps for any additional subscriptions