Simplifying Solutions
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.
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.