SQL Server Replication Agents may exhibit blocking when applying snapshot

Applies to: SQL Server 2008 DeveloperSQL Server 2008 EnterpriseSQL Server 2008 Standard

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

 
 

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.

Resolution

To work around this issue, specify a different subscriber database for each publication.

More Information

Exclusive locks are used in this situation to help avoid the possibility of replication agents becoming deadlocked with each other.