Sql-server – Log Reader Agent is not running – The job failed

replicationsql server

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.).

enter image description here

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:

enter image description here

Best Answer

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.

Related Solutions

Sql-server – “an agent job has not been created for this subscription”

I don't know how to get past the error (I'm not sure how you're trying to "run" the subscriber), but you obviously can't use Agent to distribute changes back to the publisher, because SQL Server Express does not include Agent.

This thread seems to imply you can schedule the task outside of SQL Server using replmerg.exe from the command line:

replmerg.exe 
  -Publisher    [PUBLISHER_INSTANCE_NAME] 
  -PublisherDB  [DATABASE_NAME] 
  -Publication  [PUBLICATION_NAME] 
  -Subscriber   [SERVER\SQLExpress] 
  -SubscriberDB [DATABASE_NAME]
  -Distributor  [PUBLISHER_INSTANCE_NAME] 
  -DistributorSecurityMode 1

You might also find this Stack Overflow question useful - one of the answers there shows how to do a similar thing with C# (and I didn't read any further but there might be other useful info too). This blog post might also be helpful, showing how to accomplish this with the same type of batch script, or using Windows Sync Manager, Sync Center or Replication Management Objects (RMO).

Transactional Replication – Issues with Creating 53rd Publication in SQL Server

I believe you are running into the desktop heap issue described here (support.microsoft.com/kb/949296) and here (support.microsoft.com/kb/824422).

Best Answer

Related Solutions

Sql-server – “an agent job has not been created for this subscription”

Transactional Replication – Issues with Creating 53rd Publication in SQL Server

Related Question