Anurag SharmaMicrosoft SQL Server Escalation Services
In many cases customer often have large databases published and creating a snapshot of such large database is always a pain. And, even when published databases are of manageable size, a slow or unreliable network link can further complicate the issue.
From SQL Server 2005 onwards a snapshot delivery is now capable of resuming later on from a previous interruption. This is a huge benefit in aforementioned scenarios.
SQL Server 2005/2008 a new system table MSsnapshotdeliveryprogress has been introduced to track the snapshot delivery progress. This table resides in subscription database. The table contains necessary details on what snapshot files have been applied to subscriber. For every snapshot file successfully delivered to subscriber a row is added to this table by either merge or distribution agent.
How it works
When an interrupted snapshot delivery process is restarted, the Replication (distribution/merge) agent iterates through the entire collection of snapshot files that need to be applied to the subscriber as it normally would; but with the new resumability support, the distribution/merge agent will check the MSsnapshotdeliveryprogress table to see if a file has already been applied to the subscriber by a previously interrupted snapshot delivery session prior to applying the snapshot file. If the MSsnapshotdeliveryprogress table indicates that a file has already been applied by an interrupted snapshot delivery session, the distribution/merge agent will simply skip processing of the file.
Table Structure (per BOL)
What is not resumable
This list is not comprehensive, but here are a few of the reason snapshot file would not be resumable.
How to reset the MSsnapshotdeliveryprogress table
The need may arise when you need to restart the entire snapshot processes. You can execute the procedure sp_resetsnapshotdeliveryprogress on subscriber database to reset snapshot delivery process by removing all rows from MSsnapshotdeliveryprogress table. This applies to pull subscribers only.