Troubleshooting SQL Server 2016 Transactional Replication
If you have problems with SQL Server 2016 transactional replication, review this
troubleshooting checklist to find potential solutions.
1. Install the latest SQL Server 2016 service pack.
Because some SQL Server 2016 Transactional replication bugs were fixed in SQL Server
service packs, you should install the latest SQL Server service pack.
At the time this article was written the latest SQL Server 2016 service pack was
service pack 1. You can download the SQL Server 2016 service pack 1 at here:
2. Check that you have sysadmin permissions on the SQL Server.
Only members of the sysadmin server role can configure replication, so if you have
not these permissions you cannot setting up or configure transactional replication.
3. Increase the value of the -QueryTimeout property of the Distribution Agent,
if the Distribution Agent fails on timeout.
The -QueryTimeout property of the Distribution Agent indicates the number of
seconds before the queries issued by the agent times out.
4. Check that account the MSSQLServer and SQLServerAgent services runs under
belongs to the Administrators local group and is a member of the Domain Users group.
The LocalSystem account does not have network access rights, so this account should
not be used if you want to configure transactional replication. The account the
MSSQLServer and SQLServerAgent service runs under should be a member of the
Administrators local group and a member of the Domain Users group.
5. If you change the startup account of the SQL Server Agent service to a domain
account using the Windows Service Control Manager the replication agent jobs
This problem occurs because the Windows Service Control Manager does not grant
the required permissions to the new domain account. To work around this problem,
you can use SQL Server Configuration Manager to change the startup account to
a domain account.
6. The error 20690 may occur when you set up the Publisher identity range for the table.
This is the error message text: “Cannot set up the Publisher identity range for
table %s.” To work around this problem, you should verify that appropriate ranges
were specified when the article was created, and then rerun the Snapshot Agent.
7. You can maximize the history logging by selecting 3 for the -HistoryVerboseLevel
property of the Distribution Agent and the Snapshot Agent.
This property specifies the amount of history logged during distribution operation
(for a Distribution Agent) or during a snapshot operation (for a Snapshot Agent).
The more log information you will have the easy will be find out and identify the
transactional replication problems.
8. Choose appropriate agent profile for the replication agents.
Agent profiles provide a convenient way to manage the runtime parameters for each
replication agent. For example, if the transactional replication will work through
the slow link, you can set the “Slow link agent” profile.
9. You can find out that the Distribution Agent does not deliver commands to the
subscriber even if the Distribution Agent is running.
This problem occurs when you specify the Distribution Agent to use multiple
subscription streams by setting the -SubscriptionStreams parameter to a value
of 2 or higher. To work around this problem, you can set the -SubscriptionStreams
parameter to a value of 1.
10. Perform the rowcount or rowcount and checksum validation to avoid the problems
with data consistency.
You can use the sp_table_validation system stored procedure to return or compare
the rowcount or checksum information with the specified table or indexed view.
To perform the rowcount or rowcount and checksum validation, you should run this
stored procedure at the Publisher on the publication database and at the Subscriber
on the subscription database.
11. When you synchronize the subscribers for a transactional replication, some DML
statements that should not be replicated are replicated to the subscribers.
This problem occurs when you configure a transactional replication to replicate
the execution of the stored procedure and this stored procedure creates a clustered
index on a temporary table. This bug was fixed in Cumulative update package 2 for
SQL Server 2016. You can download the Cumulative Update package 2 for SQL Server 2016
12. Snapshot Agent fails when you publish user-defined functions to SQL Server 2016
Distributor in Transactional Replication.
This problem occurs when SQL Server 2016 is acting as Distributor and SQL Server 2014
or SQL Server 2012 is acting as Publisher. This bug was fixed in Cumulative update
package 4 for SQL Server 2016. You can download the Cumulative Update package 4 for
SQL Server 2016 at here:
To work around this problem, you can create user-defined functions manually on the Subscriber.
13. The error 8144 occurs and the Distribution Agent fails for a SQL Server 2012
subscriber in Transactional Replication.
This is the error message text: “Procedure or function sp_MSreplraiserror has too
many arguments specified. (Source: MSSQLServer, Error number: 8144) Get help:
http://help/8144″ This bug was fixed in Cumulative update package 5 for
SQL Server 2016. You can download the Cumulative Update package 5 for SQL Server 2016
14. Check the Snapshot Agent and Distribution Agent history to determine what task
failed and the reason for the failure.
To view the Agent history, you can follow these steps:
1. Run Replication Monitor.
2. Choose the Snapshot Agent or Distribution Agent.
3. Right-click the appropriate transactional publication and then click Agent History.
15. Use Replication Conflict Viewer to get more information about conflict details.
The Replication Conflict Viewer displays conflicts that occur in transactional
replication during replication synchronization when queued updating subscriptions