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.
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:
- The text says
begin tranand nothing more?
- IDERA SQL Check is labelling it as
connection idling transaction?
- The status is
- I’m concerned that
CPU time, reads and writesare basically telling me that this process is frying the drive with never ending I/O, am I right?