How SQL Server 2005/2008 Replication resumes interrupted snapshot delivery
Anurag Sharma | Microsoft SQL Server Escalation Services
Background
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)
| Column name | Data type | Description |
| session_token | nvarchar(260) | Identifies the path to snapshot folder from which the file was successfully delivered. For publications that use parameterized filters, the string dynsnap will be appended to the value. |
| progress_token_hash | int | A hash value generated based on the value of progress_token that is used improve lookup efficiency for a given progress_token value. |
| progress_token | nvarchar(500) | Identifies a file that has been successfully delivered, where the value is a combination of the file name and path. |
| progress_timestamp | datetime | The date time value that indicates when a snapshot file was successfully delivered |
What is not resumable
This list is not comprehensive, but here are a few of the reason snapshot file would not be resumable.
- Index creation (.idx) files
- Declarative Referential Integrity (.dri) files (in case of merge replication only)
- Applying a .bcp file to a DTS only subscriber
- Any user written custom script
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.
How can you reset this on a Push subscription? The schema in the snapshot that was delivered was bad, and I need to re-deliver the portion that was bad but it won't, even though I updated the schema on the publisher. This is SQL 2005.