[Solved] SQL Server Replication – No replicated transactions are available
Operating system = Windows 2016 Server 1607 release
SQL Server = Microsoft SQL Server 2016 Standard Edition
Replication type = Transactional replication
SQL Server Replication – No replicated transactions are available, If you are receiving this error while configuring Microsoft SQL Server replication using transactional replication method, I would recommend to check few things before you spend too much time on Google. Things are pretty straight and common but can waste a good amount of time during troubleshooting. Items which needs to be reviewed are given as under:
- The active directory user must exist on both database servers.
- The active directory user must have db-owner rights on both databases.
- The user must have full control on the replication directory created on primary server to save the snapshots.
- The SQL Server agent windows service must be running on both servers
- While creating the publication the correct user must be selected
- The correct secondary SQL Server and database name must be chosen while configuration
In my case i somehow forgot to give full control to the configuration user on replication directory.
Solution of No replicated transactions are available error!
There are multiple other possibilities of this error which can differ according to the database and configuration. The best way to start the troubleshooting of this issue is to check the replication monitor by right clicking on the subscription and select the “Launch Replication Monitor”. This will give the in-depth details of the error and reasons. when I opened the replication monitor, the snapshot agent’s last action was about the authorization of the replication directory. I simply provided the full control rights to the DB owner user and started the initialize process again to solve the problem.
Microsoft has provided a very good artifact to troubleshoot this issue in detail at this URL. If the error still persist I would recommend to follow the documentation provided by Microsoft at this location to restart the whole process.