Allow cleanup for snapshot replication

When you setup SQL Server replication via snapshots, a new job is created on the distributor: 'Distribution clean up: distribution'

This calls a job dbo.sp_MSdistribution_cleanup that will clean up the records from the MSrepl_commands and MSrepl_transactions tables. What I didn't know was that it  also deletes any expired snapshots stored in the Replication folder.

For this process to work 3 things must be setup properly.  

• xp_cmdshell must be enabled
• SQLAgent account must have proper permissions to run xp_cmdshell
• Proper permissions must be set to delete the specified folder on the share.

Only members of the sysadmin role has permission to run xp_cmdshell, if your SQLAgent account is not a member of sysadmin role, the job could be given permission to run xp_cmdshell by using a proxy account.

Comments Closed.