An interesting issue regarding SQL Server Transaction Replication/CDC log reader agent job can’t be started with the following error:

Msg 18752, Level 16, State 1, Procedure sp_replcmds, Line 1 [Batch Start Line 229]
Only one Log Reader Agent or log-related procedure (sp_repldone, sp_replcmds, and sp_replshowcmds) can connect to a database at a time. If you executed a log-related procedure, drop the connection over which the procedure was executed or execute sp_replflush over that connection before starting the Log Reader Agent or executing another log-related procedure.

Background of the Issue:

The Distributor SQL Server instance got rebooted unexpectedly, but it came online after that the transaction replication log reader agent started failing with the above error. Eventually, it caused the transaction log file full issue, and the transaction log records were not getting cleared by the log backups because they were being held by “REPLICATION” as per column log_reuse_wait_desc.

As the subscribers were not in production, so I removed the replication even though the transaction log file was not getting cleared, and it was still showing the “REPLICATION” is holding the log records.

After analyzing the errors furthermore, I found the database was also configured for CDC feature, and the CDC job was also failing with the same above error.

Troubleshooting:

• Checked the open transactions for the publisher database by running DBCC Opentran. I found there was a long running active transaction on the database;

Transaction information for database ‘XXXX’.

Replicated Transaction Information:
Oldest distributed LSN : (266059:286834:202)
Oldest non-distributed LSN : (266035:49348:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

• Checked the hostprocess & program_name for the spid which was causing opentran, and it showed the Repl-LogReader agent was holding a long running active transaction. It surprised me because I had already removed all the publications regarding the database.

SELECT 
@@servername as servername, 
hostprocess,  hostname, 
program_name, loginame,*
FROM sys.sysprocesses 
WHERE DBID = db_id('XXX') /*Replace the XXX with your database name*/
AND open_tran<>0
ORDER BY last_batch DESC
GO

• Now, connected to Distributor SQL Server Instance,  open jobs, & checked the log read agent job for the database, but I couldn’t find the agent job there.

• As a next step, when I open the windows task manager of the distributor machine, I found one of the log reader agents was running with the same hostprocess id (14812). The logread.exe session was acting like an orphan session and it was not letting to start the SQL Server Log Reader Agent.

You can also filter it by running the command —–> tasklist | findstr logread

• Once I killed the session from windows task manager,  the CDC job stopped started working fine.

• After that, I re-setup the replication again, and everything started working fine.

Hope, you find it useful post!