Transactional Replication: When to delete the initial snapshot

Posted on

Question :

I would like to delete the initial snapshot after I setup transnational replication and everything is working fine.

When is it OK to the delete the snapshot that initializes a subscriber?

Answer :

SQL Server uses the retention setting to determine how long the snapshot is good. After the maximum retention time has expired the snapshot is of no use anymore and can be deleted. The default for this setting is 72 hours.

Check Level 4 of my replication stairway for details:

You dont have to manually cleanup the old/static snapshot files, the distribution cleanup agent will purge the old folders for you.

A bit of under the hood story of what and how snapshot files gets generated :

Basically, the snapshot agent creates snapshots for the subscribers to initialize. The snapshot folder will contain various types of files like –

  • .bcp => actual data files – either using native or character mode
  • .idx => Index creation statements
  • .sch => schema and table creation scripts
  • .dri => foreign and check constraints

These files are always kept in \uncfolder with a timestamp value for the folder name. The folder name is the publication name.

So, everytime a snapshot is generated, a new timestamp value is used for the folder name and then the snapshot files are written to that folder.

Also, if you see that your distribution agent is not cleaning up the files, you can delete the older snapshot folders. But I would still troubleshoot – why distribution agent is not cleaning it up !!

Leave a Reply

Your email address will not be published. Required fields are marked *