Troubleshooting SQL Server 2014 Snapshot Replication
If you have problems with SQL Server 2014 snapshot replication, review this troubleshooting
checklist to find potential solutions.
1. Install the latest SQL Server 2014 service pack.
Because some SQL Server 2014 snapshot 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 2014 service pack was service
pack 1. You can download the SQL Server 2014 service pack 1 at here:
https://www.microsoft.com/en-us/download/details.aspx?id=46694
2. 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 use snapshot 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.
3. Make sure that the snapshot folder is shared correctly.
Otherwise, replication agents cannot access the snapshot folder and you will get replication error.
4. 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 snapshot replication.
5. The error 14071 may occur when you set up the snapshot replication.
This is the error message text: “Could not find the Distributor or the distribution
database for the local server.” To work around this problem, ensure that the Distributor
was installed and the local server was configured as a Publisher at the Distributor.
6. 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.
7. The error 14098 may occur when you try to drop Distribution Publisher.
This problem occurs when the remote Publisher is using as Distributor. To resolve this
problem, you should disable publishing at the Publisher before attempting to drop this
relationship.
8. 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 snapshot
replication problems.
9. The error 20605 may occur for the existing snapshot of the publication.
This is the error message text: “Invalidated the existing snapshot of the publication.”
To work around this problem, you can run the Snapshot Agent again to generate a new snapshot.
10. 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 may fail.
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.
11. 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.
12. The Distribution Agent does not deliver commands to the snapshot 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.
13. The error 21331 may occur when you try to copy user script file to the snapshot
folder at the Distributor.
To work around this problem, ensure that there is enough disk space available, and that
the account under which the Snapshot Agent runs has permissions to write to the snapshot
folder and its subdirectories.
14. The error 21820 may occur when SQL Server try to write to the script file in the
snapshot folder at the Distributor.
This is the error message text: “Cannot write to the script file in the snapshot folder at
the Distributor (%ls).” To work around this problem, ensure that there is enough disk space
available and that the account under which the Snapshot Agent runs has permissions to write
to the snapshot folder and its subdirectories.
15. 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 snapshot publication and then click Agent History.