Log Reader Agent is not running - The job failed

Question

I created a transactional replication on PRE-PROD (via TSQL) and everything works as expected. On PROD platform replication is not working. While inspecting Replication monitor, I get this error message for Log Reader Agent:

The job failed. The Job was invoked by User sa. The last step to run was step 2 (Run agent.).

Any idea why I get this error?

SQL Agent is running under context of "NT AUTHORITY\NETWORKSERVICE". Configuration and security seems to be identical on PROD and PRE-PROD.

The publication and subscription have been correctly created. Everything is running on a single server, in the context of a user that is sysadmin and db_owner for publication and subscription DB:

Answer 1

The recommended approach is to have replication agents run under Windows accounts, not the SQL Server Agent service account, and the accounts should be granted only the required permissions.

Create a dedicated Windows account for the Log Reader Agent, grant it the appropriate permissions covered in Replication Agent Security Model, and use this account for your Log Reader Agent Security.

Answer 2

I have had this problem, since then I never use NT AUTHORITY\NETWORKSERVICE for anything anymore.

I create dedicate active directory accounts to deal with replication. For example DEV\SQLReplication for the dev environment, and MY_DOMAIN\SQLReplication for production.

Give them a good password and you are good to go.

another thing I do sometimes, depending on the size of the snapshot to be generated, is to drop the subscription and create it back from scratch. I only use this when everything else fails, Including reinitialization.

Here you can see the scripts that I use to create a subscription in Transactional Replication.

Here you can see the scripts I use to check what has been read by the log reader and whatever and get extra information about any errors related to this