cleanup old transactional replication snapshot files

Question

I have a transactional replication between a publisher and a subscriber where the subscriber also acts as the distributor. I am facing storage issues as old snapshot files are not being auto cleaned up after successful application via distributor.

I have to manually clean up the folder containing the old replication snapshot files and scripts.

I'm wondering if there's a way to set up a job that deletes the snapshots older than x days to ensure storage is freed up.

I've already looked into the Replication agent jobs clean-up category but couldn't find anything suitable. I'm using SQL Server 2016 Standard.

Answer 1

Hi @Abimbola Adeniran

Old snapshots are deleted by the distribution cleanup agent. SQL Distribution Cleanup job automatically purges the Snapshot folder of all snapshot files older than 72 hours (default).

If you need to keep the Snapshot files longer, increase the @max_distretention in the Distribution clean up: distribution cleanup job.

EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72

Make sure the distribution cleanup job is running successfully. Make sure SQL Server agent has full rights on the snapshot folder.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

Hello,

I know how about that job but it doesn’t cleanup the snapshot files. All perms are fine, checked that.

I read somewhere that it uses xp_cmdshell under the hood but that’s disabled on our SQL Server instance and by default because of its vulnerability.

I however prepared a Powershell sheet for the cleanup but wanted to troubleshoot issues with the distribution cleanup job