SQL Server Replication Agents may exhibit blocking when applying snapshot

View products that this article applies to.

Symptoms

Consider the following scenario:

You are using SQL Server replication.
You have multiple publications that publish data into one database at the subscriber(s).

While applying the initial snapshot(s), you notice that only one publication is able to apply its snapshot at a time.

You may see a wait resource similar to the following when reviewing SQL activity:

APP: 18:16384:[snapshot_delivery_in_progress_Tr]:(9bcdaf92)
APP: 5:16384:[snapshot_delivery_in_progress_Er]:(3c3b7db9)

Querying for locking behavior may show resources similar to the following:

APP 16384:[appname]:(fbe42d68)  X
APP 16384:[snapshot_del]:(9bcdaf92)  X

↑ Back to the top

Cause

This behavior is by design. It occurs because an application lock is used to prevent multiple replication agents from concurrently applying snapshots of different publications to the same subscriber database. Because the application lock contains the name of the subscriber database, any publications that publish into the same subscriber database will be impacted. The result is that only one snapshot can be inserted into the subscriber database at a given time.

↑ Back to the top