#StackBounty: #sql-server #high-availability #sql-server-2019 #alwayson #availability-group SQL Server :: Replication Distribution Agen…

Bounty: 50

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:

  • session_id = 69
  • text = begin tran
  • login_time = 2020-09-08 18:40:57.153
  • program_name = Replication Distribution Agent
  • status = sleeping
  • cpu_time = 1362772
  • memory_usage = 4
  • total_scheduled_time = 1689634
  • total_elapsed_time = 22354857
  • last_request_start_time = 2020-09-28 16:28:39.433
  • reads = 18607577
  • writes = 5166597
  • logical_reads = 112256365

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?


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.