First Steps in Troubleshooting Replication

Why does Microsoft SQL Support ask me all those questions?  Why can’t they just show me how it work or fix the problem?  Well wish it was that simple, but for us to help you we need to get the “big picture”.  Below are some of the questions we ask and initial troubleshooting steps we’ll take.

Want to surprise your SQL Support Engineer?  See how many questions you can answer and have ready before placing that initial troubleshooting call.

The Big Picture

The Servers

• How many SQL Servers are involved and what are their roles?
• How are the Publisher, Distributor, Subscriber(s) roles configured?
• Is the Publisher and Distributor the same SQL server?
• Is the Distributor its own SQL server?
• What are the names of the key servers?
• How many subscribers, 1, 100, 1000?
• In what time zones are the different servers?
• What versions/builds are they each running?
• What is the data and/or hardware backup plan for each server?

The Problem

• Are the servers in production? If in development, when is the project expected to go live?
• What is the exact error? Can we see it in a log, pop-up (ctrl-c), or screen shot?
• Can we easily reproduce the problem? If we can, under what circumstances does it work, and when does it fail?
• Can we reproduce the problem using sample database Northwind, Pubs, AdventureWorks, or even a new database?
• Is the problem intermittent? If not, when does it occur?
• Does the problem only occur on these computers or can it be reproduced on a set of test computers?
• When was the last time this feature/function worked?
• What steps do you follow to get around the problem and keep the business running?

General Troubleshooting Data and Logs

NEW: For Merge Replication the ReplMerge.log file which is ON by default. The minimal default logging may provide enough detail to resolve issue without needing to collect verbose log or Profiler trace.

C:\Program Files\Microsoft SQL Server\100\COM\replmerge.log

We’ll often collect SQLDiag from all SQL servers which collects SQL Server error log, system configuration, and performance data. Looking at the error log may uncover the root cause. For example, SQL Server not responding may be caused from 17883 “scheduler hung” problem and not a connectivity issue. Even if the problem is not occuring right now, SQLDiag shows build numbers, server name, IP address, database name, etc.

c:> “C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLDIAG.exe” /ISD_Detailed.XML

Export the Windows Application and System Event logs and TEXT instead of EVT.  Again, a history of the problem may show as an underlying system issue. Many times we’ve seen customer having problem with a “suspect” database only to discover the System event log contains IO errors with the disk drives.

Replication Configurations

• Are the Publisher and Distributor the same computer or separate?
• How many subscribers?
• Are they Push, Pull, anonymous, named, or a combination of all?
• Do you have the Replication publication script. This can be valuable in identifying article, filters, and publication properties.

  How to: Script Replication Objects (SQL Server Management Studio)
  http://msdn.microsoft.com/en-us/library/ms151213.aspx

  Generate SQL Script (Replication Objects)
  http://msdn2.microsoft.com/en-us/library/ms188503.aspx

• Do you have “-output” parameter for the Agent? http://support.microsoft.com/kb/312292 or video walk-though on http://blogs.msdn.com/chrissk/archive/2010/01/05/walk-through-of-kb-article-312292-how-to-enable-replication-agents-for-logging-to-output-files-in-sql-server.aspx.
• How large is the Distribution database? If under 4gig, take backup to preserve Agent history and errors. If not, you can export the Replication tables into a copy of the Distribution database using script similar to the one below.

Published Database Collection Script

–Publisher Replication Metadata Collection Script

–Execute on the Publisher: modify backup location below as needed

—

CREATE DATABASE MS_PublisherMetadata

GO

USE <replace with published database name>

GO

–Possible large tables, collect as needed.

–SELECT * INTO SubscriberMetadata..MSmerge_contents FROM MSmerge_contents

Go

–SELECT * INTO SubscriberMetadata..MSmerge_current_partition_mappings FROM MSmerge_current_partition_mappings

Go

–SELECT * INTO SubscriberMetadata..MSmerge_past_partition_mappings FROM MSmerge_past_partition_mappings

Go

SELECT * INTO MS_PublisherMetadata..MSmerge_agent_parameters FROM MSmerge_agent_parameters with (nolock)

Go

SELECT * INTO MS_PublisherMetadata..MSmerge_altsyncpartners FROM MSmerge_altsyncpartners with (nolock)

Go

SELECT * INTO MS_PublisherMetadata..MSmerge_articlehistory FROM MSmerge_articlehistory with (nolock)

Go

SELECT * INTO MS_PublisherMetadata..MSmerge_conflicts_info FROM MSmerge_conflicts_info with (nolock)

Go

SELECT * INTO MS_PublisherMetadata..MSmerge_dynamic_snapshots FROM MSmerge_dynamic_snapshots with (nolock)

Go

SELECT * INTO MS_PublisherMetadata..MSmerge_errorlineage FROM MSmerge_errorlineage with (nolock)

Go

SELECT * INTO MS_PublisherMetadata..MSmerge_generation_partition_mappings FROM MSmerge_generation_partition_mappings with (nolock)

Go

SELECT * INTO MS_PublisherMetadata..MSmerge_genhistory FROM MSmerge_genhistory with (nolock)

Go

SELECT * INTO MS_PublisherMetadata..MSmerge_history FROM MSmerge_history with (nolock)

Go

SELECT * INTO MS_PublisherMetadata..MSmerge_identity_range FROM MSmerge_identity_range with (nolock)

Go

SELECT * INTO MS_PublisherMetadata..MSmerge_log_files FROM MSmerge_log_files with (nolock)

Go

SELECT * INTO MS_PublisherMetadata..MSmerge_metadataaction_request FROM MSmerge_metadataaction_request with (nolock)

Go

SELECT * INTO MS_PublisherMetadata..MSmerge_partition_groups FROM MSmerge_partition_groups with (nolock)

Go

SELECT * INTO MS_PublisherMetadata..MSmerge_replinfo FROM MSmerge_replinfo with (nolock)

Go

SELECT * INTO MS_PublisherMetadata..MSmerge_sessions FROM MSmerge_sessions with (nolock)

Go

SELECT * INTO MS_PublisherMetadata..MSmerge_settingshistory FROM MSmerge_settingshistory with (nolock)

Go

SELECT * INTO MS_PublisherMetadata..MSmerge_supportability_settings FROM MSmerge_supportability_settings with (nolock)

Go

SELECT * INTO MS_PublisherMetadata..MSmerge_tombstone FROM MSmerge_tombstone with (nolock)

Go

SELECT * INTO MS_PublisherMetadata..sysmergearticles FROM sysmergearticles with (nolock)

Go

SELECT * INTO MS_PublisherMetadata..sysmergepartitioninfo FROM sysmergepartitioninfo with (nolock)

Go

SELECT * INTO MS_PublisherMetadata..sysmergepublications FROM sysmergepublications with (nolock)

Go

SELECT * INTO MS_PublisherMetadata..sysmergeschemaarticles FROM sysmergeschemaarticles with (nolock)

Go

SELECT * INTO MS_PublisherMetadata..sysmergeschemachange FROM sysmergeschemachange with (nolock)

Go

SELECT * INTO MS_PublisherMetadata..sysmergesubscriptions FROM sysmergesubscriptions with (nolock)

Go

SELECT * INTO MS_PublisherMetadata..sysmergesubsetfilters FROM sysmergesubsetfilters with (nolock)

Go

SELECT * INTO MS_PublisherMetadata.dbo.MSpeer_conflictdetectionconfigresponse FROM dbo.MSpeer_conflictdetectionconfigresponse WITH (nolock)

Go

SELECT * INTO MS_PublisherMetadata.dbo.sysreplservers FROM dbo.sysreplservers WITH (nolock)

Go

SELECT * INTO MS_PublisherMetadata.dbo.sysarticles FROM dbo.sysarticles WITH (nolock)

Go

SELECT * INTO MS_PublisherMetadata.dbo.sysarticlecolumns FROM dbo.sysarticlecolumns WITH (nolock)

Go

SELECT * INTO MS_PublisherMetadata.dbo.sysschemaarticles FROM dbo.sysschemaarticles WITH (nolock)

Go

SELECT * INTO MS_PublisherMetadata.dbo.syspublications FROM dbo.syspublications WITH (nolock)

Go

SELECT * INTO MS_PublisherMetadata.dbo.syssubscriptions FROM dbo.syssubscriptions WITH (nolock)

Go

SELECT * INTO MS_PublisherMetadata.dbo.sysarticleupdates FROM dbo.sysarticleupdates WITH (nolock)

Go

SELECT * INTO MS_PublisherMetadata.dbo.MSpub_identity_range FROM dbo.MSpub_identity_range WITH (nolock)

Go

SELECT * INTO MS_PublisherMetadata.dbo.systranschemas FROM dbo.systranschemas WITH (nolock)

Go

SELECT * INTO MS_PublisherMetadata.dbo.MSpeer_lsns FROM dbo.MSpeer_lsns WITH (nolock)

Go

SELECT * INTO MS_PublisherMetadata.dbo.MSpeer_request FROM dbo.MSpeer_request WITH (nolock)

Go

SELECT * INTO MS_PublisherMetadata.dbo.MSpeer_response FROM dbo.MSpeer_response WITH (nolock)

Go

SELECT * INTO MS_PublisherMetadata.dbo.MSpeer_topologyrequest FROM dbo.MSpeer_topologyrequest WITH (nolock)

Go

SELECT * INTO MS_PublisherMetadata.dbo.MSpeer_topologyresponse FROM dbo.MSpeer_topologyresponse WITH (nolock)

Go

SELECT * INTO MS_PublisherMetadata.dbo.MSpeer_originatorid_history FROM dbo.MSpeer_originatorid_history WITH (nolock)

Go

SELECT * INTO MS_PublisherMetadata.dbo.MSpeer_conflictdetectionconfigrequest FROM dbo.MSpeer_conflictdetectionconfigrequest WITH (nolock)

Go

–Change backup location if needed.

BACKUP DATABASE MS_PublisherMetadata TO DISK=‘c:\MS_PublisherMetadata.bak’

GO

Distribution Database Collection Script

–Distribution Database Replication Metadata Collection Script

–Execute On Distributor: modify backup location below as needed

–Assumes Distribution database names is ‘distribution’

CREATE DATABASE MS_DistBackup

Go

USE MS_DistBackup

go

SELECT * INTO MSarticles from Distribution..MSarticles with (nolock)

SELECT * INTO MScached_peer_lsns from Distribution..MScached_peer_lsns with (nolock)

SELECT * INTO MSdistribution_agents from Distribution..MSdistribution_agents with (nolock)

SELECT * INTO MSdistribution_history from Distribution..MSdistribution_history with (nolock)

SELECT * INTO MSlogreader_agents from Distribution..MSlogreader_agents with (nolock)

SELECT * INTO MSlogreader_history from Distribution..MSlogreader_history with (nolock)

SELECT * INTO MSmerge_agents from Distribution..MSmerge_agents with (nolock)

SELECT * INTO MSmerge_articlehistory from Distribution..MSmerge_articlehistory with (nolock)

SELECT * INTO MSmerge_history from Distribution..MSmerge_history with (nolock)

SELECT * INTO MSmerge_identity_range_allocations from Distribution..MSmerge_identity_range_allocations with (nolock)

SELECT * INTO MSmerge_sessions from Distribution..MSmerge_sessions with (nolock)

SELECT * INTO MSmerge_subscriptions from Distribution..MSmerge_subscriptions with (nolock)

SELECT * INTO MSpublication_access from Distribution..MSpublication_access with (nolock)

SELECT * INTO MSpublications from Distribution..MSpublications with (nolock)

SELECT * INTO MSpublicationthresholds from Distribution..MSpublicationthresholds with (nolock)

SELECT * INTO MSpublisher_databases from Distribution..MSpublisher_databases with (nolock)

SELECT * INTO MSqreader_agents from Distribution..MSqreader_agents with (nolock)

SELECT * INTO MSqreader_history from Distribution..MSqreader_history with (nolock)

SELECT * INTO MSrepl_backup_lsns from Distribution..MSrepl_backup_lsns with (nolock)

SELECT Top 100 * INTO MSrepl_commands_OLDEST from Distribution..MSrepl_commands with (nolock)

       order by xact_seqno asc

SELECT Top 100 * INTO MSrepl_commands_NEWEST from Distribution..MSrepl_commands with (nolock)

       order by xact_seqno desc

SELECT * INTO MSrepl_errors from Distribution..MSrepl_errors with (nolock)

SELECT * INTO MSrepl_identity_range from Distribution..MSrepl_identity_range with (nolock)

SELECT * INTO MSrepl_originators from Distribution..MSrepl_originators with (nolock)

SELECT top 100 * INTO MSrepl_transactions_OLDEST from Distribution..MSrepl_transactions with (nolock)

       order by xact_seqno asc

SELECT top 100 * INTO MSrepl_transactions_NEWEST from Distribution..MSrepl_transactions with (nolock)

       order by xact_seqno desc

SELECT * INTO MSrepl_version from Distribution..MSrepl_version with (nolock)

SELECT * INTO MSreplication_monitordata from Distribution..MSreplication_monitordata with (nolock)

SELECT * INTO MSsnapshot_agents from Distribution..MSsnapshot_agents with (nolock)

SELECT * INTO MSsnapshot_history from Distribution..MSsnapshot_history with (nolock)

SELECT * INTO MSsubscriber_info from Distribution..MSsubscriber_info with (nolock)

SELECT * INTO MSsubscriber_schedule from Distribution..MSsubscriber_schedule with (nolock)

SELECT * INTO MSsubscriptions from Distribution..MSsubscriptions with (nolock)

SELECT * INTO MSsync_states from Distribution..MSsync_states with (nolock)

SELECT * INTO MStracer_history from Distribution..MStracer_history with (nolock)

SELECT * INTO MStracer_tokens from Distribution..MStracer_tokens with (nolock)

Go

–Change backup location if needed.

BACKUP DATABASE MS_DistBackup to disk=‘c:\MS_DistBackup.bak’

Go

Subscriber Database Collection Script

–Execute on the Subscriber database

–Note: Some errors may appear as not all tables

—     may exists on all subscribers

—

create database MS_SubscriberMetadata

go

–Use <replace with subscriber database name>

go

–Merge Replication Tables

SELECT * INTO MS_SubscriberMetadata.dbo.MSsnapshotdeliveryprogress FROM dbo.MSsnapshotdeliveryprogress WITH (nolock)

Go

SELECT * INTO MS_SubscriberMetadata.dbo.MSmerge_conflict_MergeProduct_Product FROM dbo.MSmerge_conflict_MergeProduct_Product WITH (nolock)

Go

SELECT * INTO MS_SubscriberMetadata.dbo.MSmerge_articlehistory FROM dbo.MSmerge_articlehistory WITH (nolock)

Go

SELECT * INTO MS_SubscriberMetadata.dbo.MSrepl_errors FROM dbo.MSrepl_errors WITH (nolock)

Go

SELECT * INTO MS_SubscriberMetadata.dbo.MSmerge_history FROM dbo.MSmerge_history WITH (nolock)

Go

SELECT * INTO MS_SubscriberMetadata.dbo.MSmerge_agent_parameters FROM dbo.MSmerge_agent_parameters WITH (nolock)

Go

SELECT * INTO MS_SubscriberMetadata.dbo.MSmerge_replinfo FROM dbo.MSmerge_replinfo WITH (nolock)

Go

SELECT * INTO MS_SubscriberMetadata.dbo.sysmergearticles FROM dbo.sysmergearticles WITH (nolock)

Go

SELECT * INTO MS_SubscriberMetadata.dbo.MSmerge_conflicts_info FROM dbo.MSmerge_conflicts_info WITH (nolock)

Go

SELECT * INTO MS_SubscriberMetadata.dbo.MSmerge_metadataaction_request FROM dbo.MSmerge_metadataaction_request WITH (nolock)

Go

SELECT * INTO MS_SubscriberMetadata.dbo.MSmerge_errorlineage FROM dbo.MSmerge_errorlineage WITH (nolock)

Go

SELECT * INTO MS_SubscriberMetadata.dbo.sysmergepublications FROM dbo.sysmergepublications WITH (nolock)

Go

SELECT * INTO MS_SubscriberMetadata.dbo.MSmerge_identity_range FROM dbo.MSmerge_identity_range WITH (nolock)

Go

SELECT * INTO MS_SubscriberMetadata.dbo.sysmergepartitioninfo FROM dbo.sysmergepartitioninfo WITH (nolock)

Go

SELECT * INTO MS_SubscriberMetadata.dbo.sysmergeschemaarticles FROM dbo.sysmergeschemaarticles WITH (nolock)

Go

–potential high volume table, collect as needed

SELECT * INTO MS_SubscriberMetadata.dbo.MSmerge_tombstone FROM dbo.MSmerge_tombstone WITH (nolock)

Go

–potential high volume table, collect as needed

SELECT * INTO MS_SubscriberMetadata.dbo.MSmerge_contents FROM dbo.MSmerge_contents WITH (nolock)

Go

–potential high volume table, collect as needed

SELECT * INTO MS_SubscriberMetadata.dbo.MSmerge_genhistory FROM dbo.MSmerge_genhistory WITH (nolock)

Go

SELECT * INTO MS_SubscriberMetadata.dbo.MSmerge_settingshistory FROM dbo.MSmerge_settingshistory WITH (nolock)

Go

SELECT * INTO MS_SubscriberMetadata.dbo.sysmergeschemachange FROM dbo.sysmergeschemachange WITH (nolock)

Go

SELECT * INTO MS_SubscriberMetadata.dbo.sysmergesubsetfilters FROM dbo.sysmergesubsetfilters WITH (nolock)

Go

SELECT * INTO MS_SubscriberMetadata.dbo.MSdynamicsnapshotviews FROM dbo.MSdynamicsnapshotviews WITH (nolock)

Go

SELECT * INTO MS_SubscriberMetadata.dbo.MSdynamicsnapshotjobs FROM dbo.MSdynamicsnapshotjobs WITH (nolock)

Go

SELECT * INTO MS_SubscriberMetadata.dbo.MSmerge_altsyncpartners FROM dbo.MSmerge_altsyncpartners WITH (nolock)

Go

SELECT * INTO MS_SubscriberMetadata.dbo.MSmerge_partition_groups FROM dbo.MSmerge_partition_groups WITH (nolock)

Go

SELECT * INTO MS_SubscriberMetadata.dbo.sysmergesubscriptions FROM dbo.sysmergesubscriptions WITH (nolock)

Go

SELECT * INTO MS_SubscriberMetadata.dbo.MSmerge_generation_partition_mappings FROM dbo.MSmerge_generation_partition_mappings WITH (nolock)

Go

–potential high volume table, collect as needed

SELECT * INTO MS_SubscriberMetadata.dbo.MSmerge_current_partition_mappings FROM dbo.MSmerge_current_partition_mappings WITH (nolock)

Go

SELECT * INTO MS_SubscriberMetadata.dbo.MSmerge_past_partition_mappings FROM dbo.MSmerge_past_partition_mappings WITH (nolock)

Go

SELECT * INTO MS_SubscriberMetadata.dbo.MSmerge_sessions FROM dbo.MSmerge_sessions WITH (nolock)

Go

SELECT * INTO MS_SubscriberMetadata.dbo.MSmerge_dynamic_snapshots FROM dbo.MSmerge_dynamic_snapshots WITH (nolock)

Go

SELECT * INTO MS_SubscriberMetadata.dbo.MSmerge_supportability_settings FROM dbo.MSmerge_supportability_settings WITH (nolock)

Go

SELECT * INTO MS_SubscriberMetadata.dbo.MSmerge_log_files FROM dbo.MSmerge_log_files WITH (nolock)

Go

–Transactional Replication Tables

SELECT * INTO MS_SubscriberMetadata.dbo.MSreplication_subscriptions FROM dbo.MSreplication_subscriptions WITH (nolock)

Go

SELECT * INTO MS_SubscriberMetadata.dbo.MSsubscription_agents FROM dbo.MSsubscription_agents WITH (nolock)

Go

SELECT * INTO MS_SubscriberMetadata.dbo.MSreplication_objects FROM dbo.MSreplication_objects WITH (nolock)

Go

SELECT * INTO MS_SubscriberMetadata.dbo.MSsnapshotdeliveryprogress FROM dbo.MSsnapshotdeliveryprogress WITH (nolock)

Go

–Change backup location if needed.

Backup database MS_SubscriberMetadata to disk=‘c:\MS_SubscriberMetadata.bak’

Go