sql-serverhigh-availabilitysql-server-2019alwaysonavailability-group

SQL Server :: Replication Distribution Agent never ending

I'm running SQL Server 2019 Always ON Availability Group with an asynchronous replication.

I use a free tool called IDERA SQL Check and I have spotted the SPID 69 which program name is Replication Distribution Agent. It's always there, staring at me like a bored cat.

enter image description here

This SPID 69 is pointing to a specific database which is mirrored I investigated it with this the query:

select 
s.session_id
,login_name
,login_time
,host_name
,program_name
,status
,cpu_time
,memory_usage
,total_scheduled_time
,total_elapsed_time
,last_request_start_time
,reads
,writes
,logical_reads
from sys.dm_exec_sessions s
inner join sys.dm_exec_connections c
on s.session_id = c.session_id
outer apply sys.dm_exec_sql_text(c.most_recent_sql_handle) st
where s.is_user_process = 1
and s.open_transaction_count > 0;

Which gave me this response:

Now, on internet I find that when you see Replication Distribution Agent is all good, that agent should be going and there should be no problem. But why:

  1. The text says begin tran and nothing more?
  2. IDERA SQL Check is labelling it as connection idling transaction?
  3. The status is sleeping?
  4. I'm concerned that CPU time, reads and writes are basically telling me that this process is frying the drive with never ending I/O, am I right?
Solution

  • This is perfectly normal.

    The replication distribution agent is effectively running continuously to scan the transactions on your source to be able to send them to the replicas. Because it needs to capture these and forward them, it has to run continuously.

    It is not frying your drive - unless your transaction rate is so high that that is actually frying your drive. It shows high reads in an incremental manner - this is cumulative values and not a snapshot of current. That suggests that it has read the equivalent of 141GB over 20 days - not particularly heavy use.