Have you ever encountered a situation where your SQL Server’s data volume is running out of free space? This can be a critical issue, especially if you have replication snapshots that consume a significant amount of disk space. In this article, we will discuss a strategy to clear space on your SQL Server volume by moving the replication snapshot folder to a different location.

By default, the replication snapshot folder is located at C:\Program Files\Microsoft SQL Server\MSSQL\ReplData\. However, you can change this location by executing the following command:

exec sp_changedistpublisher @publisher = 'SQLServerPublisherName' , @property = 'working_directory' , @value = 'H:\MSSQL\repldata\'

It is important to note that the service account for the Snapshot Agent must have read/write permissions to the new location. Additionally, the Merge Agent or Distribution Agent needs read permissions to the new location. If you have pulled subscriptions, you will also need to re-create any sharing settings on the new location.

Once you start the snapshot agent for a publication, it will create a snapshot in the new location provided in the @Value parameter. The snapshot will be stored in subfolders with the format \unc\name of snapshot\YYYYMMDDHHMMSS\. It is worth mentioning that the distribution cleanup agent will not clean up the snapshots in the old folder location. You can delete them at your leisure, but make sure you don’t need them to resync a new subscription.

To verify the change and view the new snapshot folder location, you can execute the following query:

select name, working_directory from msdb.dbo.MSdistpublishers

If you have multiple publications and want to generate a script to start the snapshot agent job for each publication, you can use the following query:

select distinct j.name, tsql = 'exec msdb.dbo.sp_start_job ''' + j.name + '''' 
from msdb.dbo.sysjobs j 
inner join msdb.dbo.syscategories c on j.category_id = c.category_id 
where c.name = 'REPL-Snapshot' and j.enabled = 1

It is not recommended to execute this script for all publications at once, but it can be handy for servers with many publications.

For further reading, you can refer to the following resources:

• Replication Snapshot Folder Cleanup
• MSDN – sp_changedistpublisher

By following these steps, you can effectively manage the replication snapshot folder in your SQL Server and ensure that your data volume has enough free space to operate smoothly.