In the distribution database, there are around 23m records (95% of total records) older than the retention period (720 hours or 30 days) which are not getting cleared down. The oldest transaction entry_time is 2014. It seems to recognise that these old records are there as they keep the job running continuously. New transactions that either get delivered or hit max retention of 30 days get cleared down so not in danger of growing exponentially but having a distribution that only requires 0.5GB but currently sits at 10GB.
To clarify further, the job does not fail, nor am I seeing any locking or blocking or waits of any kind. Looking at dm_exec_requests/dm_exec_sessions this, this shows as "running" with DEL stmt against sp_MSdelete_publisherdb_trans.
All publications are set with immediate_sync OFF - either way these should abide by max retention set.
I feel I have exhausted a number of articles and forums and cannot get to the bottom of this.
Does anyone have any idea of what this might be or recommendations of how I could take investigate this further?