cleanup old transactional replication snapshot files

Abimbola Adeniran 21 Reputation points
2024-04-18T17:46:01.6433333+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,742 questions
Windows Server
Windows Server
A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.
12,156 questions
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
42 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. CosmogHong-MSFT 23,246 Reputation points Microsoft Vendor
    2024-04-19T06:29:14.03+00:00

    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".

  2. Abimbola Adeniran 21 Reputation points
    2024-04-21T23:59:42.69+00:00

    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

    0 comments