Pending Replication Transactions in the Transaction Log

The below query gets the count of pending replication transactions from the transaction log. The log reader agent sends these txns to the distribution database and high no.of pending txns in the log is not a good sign of replication performance as well as affects database performance due to log growth. The log reader agent is not present in snapshot replication.

The VLFs in the log are marked as active and cannot be reused until these txns are sent to the distribution database. This can make the log file to grow and since log file is not instant file initialized, it affects the performance.

Select instance_name as DBName,cntr_Value as [PendingTransInTransactionLog]
from sys.dm_os_performance_counters A inner join Sys.databases B on A.instance_name=B.name and B.is_published=1 where LTRIM(counter_name) like '%Repl. Pending Xacts%'

Advertisement

Changing replication job names

After configuring replication,bunch of jobs are created under SQL server agent. The job names can some times be vague and you may have to check the Tsql step code or job category to determine what type of job it is.

It is, however, possible to rename the job name to something that makes more sense. This can help greatly when you need to check on the job(especially for someone who is not very familiar with the replication terms). Renaming the job name should not cause any issues with the replication. You can also update the internal agents name in the distribution database. This is not required but I think it is worth doing, if there are any scripts that access these tables to pull the corresponding job information.

In the below example, I configured a simple transactional replication and it created three jobs:
1. Snapshot Agent
2. Distribution Agent
3. Log Reader Agent

Step 1:
Verify the job name for each one here:
Use distribution
go
select * from MSdistribution_agents where subscriber_id=0
select * from MSsnapshot_agents
Select * from MSlogreader_agents

Step 2:
Rename the job under SQL agent directly or use sp_update_job to something that’s more easy to understand.
Step 3:
Update the job names in the corresponding Agents table.
Use [Distribution]
GO
Update MSdistribution_agents set Name='SourceDB_TargetDB_Replication_DistributionAgent'
where Name='ANULU\SQL2016-SourceDB-SourceDB_Publication-ANULU\SQL2016-3'

Update MSsnapshot_agents set Name='SourceDB_TargetDB_Replication_SnapshotAgent'
where Name='ANULU\SQL2016-SourceDB-SourceDB_Publication-2'

Update MSLogReader_agents set Name='SourceDB_Replication_LogReader'
where Name='ANULU\SQL2016-SourceDB-2'