다음을 통해 공유

Replication: Merge Fails with error : “The process could not increment the lineage version”

  • 아티클

I have come across this interesting issue in which we have the following scenario:

Merge Replication

All servers are running SQL 2008

When merge replication is processing certain published articles, we see that the merge agent is blocked, and is being blocked by another spid executing another task coming from the same merge agent.

A close look through a profiler trace, we see:

Example:

Spid 59 is blocking on spid 69.

Spid 59:   

59 runnable                            0x0063                    562  SELECT           PREEMPTIVE_OS_WAITFORSINGLEOBJEC        LPC CLAUDY-MERGETEST-MERGETEST-CLAUDY2                                                                           

Spid 69:  

69 suspended                          59         7 KEY: 6:72057594057916416 (1f02b882c015)       0x0005      296281 UPDATE           LCK_M_X    LPC    CLAUDY-MERGETEST-MERGETEST-CLAUDY2       

                                                                   

Spid 69 –> sp_MSuplineageversion

Spid 59 –> sp_msenumchangesdirect

Points to mention:

a. same merge agent for both spids

b. LPC being used

c. Waittype of “preemptive_os_waitforsingleobject” for the blocking spid

–> please read the CSS Blog for more information in regards to this waittype (https://blogs.msdn.com/psssql/archive/2009/11/03/the-sql-server-wait-type-repository.aspx)

d. 0x0063 (for spid 59) indicates AsyncNetworkIO

After looking further in depth of this issue, the workaround to use if you are running into this issue is:

Create an alias (the alias name has to be the same name as the server as merge collects information in regards to the servers involved from system tables)

SQL Server Configuration Manager
SQL Native Client
Aliases
Create a new alias with the SQL Server Name

Doing so will Force Merge agent to use TCP/IP (this can be seen in sysprocesses --> net_library column)

Once this is set up, run merge and verify in the sysprocesses that the spids are now using TCPIP.

** More info on this topic when available **

Comments

  • Anonymous
    April 20, 2010
    Cluadia, I'm running into this issue with a publication (1 publisher, around 20 subscribers) every few minutes. Can you explain a little bit more detailed what you mean with "create an alias"? I would like to try this because merge replication occurs to be very slow. Thank you!

  • Anonymous
    July 22, 2013
    Thank you for your post. It helped me for a similar issue (2 spid locked, one of them by ASYNC_NETWORK_IO). The workaround you suggested worked fine.