locked
Error in Replication: connection was chosen as the victim in a deadlock RRS feed

  • Question

  • I'm using a merge replication with SQL 2008 at server and SQL Express 2008 on subscribers.

    We are using around 100 subscribers.

    I'm getting the following error once two subscribers replicate at the same time. The final number of transaction uploaded to the server is always arong 100 and a similar number in the downloads, so the amount of transaction is not an issue.

    I'he been playing with the merge profile with no luck.

    The merge process could not replicate one or more INSERT statements to the 'Publisher'. A stored procedure failed to execute. Troubleshoot by using SQL Profiler. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200990)
    Get help: http://help/MSSQL_REPL-2147200990

    A query executing on Publisher 'xxxxx' failed because the connection was chosen as the victim in a deadlock. Please rerun the merge process if you still see this error after internal retries by the merge process. (Source: MSSQLServer, Error number: 1205)
    Get help: http://help/1205

    Any idea?

    This is getting to a critical point where transactions are not being uploaded to the server.

     Additonally, there are a lot of blocks between the subscribers, and they are mainly associated with table

    MSmerge_partition_groups

     

    Friday, August 20, 2010 10:19 PM

All replies

  • L tranc:

    Did you try to reindex the merge system tables ? reindexing will help improving hte response time and preventing deadlocks.

    Also  one other thing, are you replicating in your publisher any user triggers? it seems that a stored procedure is doing an insert statement in your subscriber and a trigger is detecting the insert statement firing it to a table that you did not publish or that does not exist on the subscriber.

     

    Sunday, August 22, 2010 1:15 AM
  • Wissam,

    If I re-index the tables for the replication or the tables for the application I get around five subscriber replicate but soon or later the nightmare start again.

     

    I have being doing this for a while to, at least, get some subscriber replicate.

     

    We are no replicating any programability object or user trigger. I'm assuming thet my perfomances issue are related with the use of @use_partition_groups = true. I've been reading other tickets about bad news turning this flag off.

    Sunday, August 22, 2010 1:45 AM
  • L tranc,

    what does select count(*) from msmerge_partition_groups (nolock) say ??

    Sunday, August 22, 2010 2:01 AM
  • 209 records
    Sunday, August 22, 2010 2:09 AM
  • and this

    SELECT COUNT(*) FROM MSmerge_current_partition_mappings (nolock)

    returns 417133 records

    Sunday, August 22, 2010 2:21 AM
  • can you post the detailed output of the merge agent using -OutputVerboseLevel 3 and -Output volumeLetter\file.txt

    where volumeletter\file.log would be like c:\mylog.txt, that could be of more use to looking into the stack trace.

     

    Sunday, August 22, 2010 2:29 AM
  • Wissam,

     

    For some reason, if I try to change my merge profile, using the GUI, the option -OutputVerboseLevel is telling me that the higher value is 2.

     

    Also, if I try to chage the parameter -Output to a value as c:\mylog.txt, is telling me that this parameter shoel be an integer.

    This is what I did:

    -OutputVerboseLevel = 2

    -OutMessageFile = c:\mylog.txt

    Hopefuly this could return some info.

    Monday, August 23, 2010 4:02 AM
  • Unfortunately, the file wasn't created.
    Monday, August 23, 2010 4:11 AM
  • Hi L,

     

    Since the error message has said “A query executing on Publisher 'xxxxx' failed because the connection was chosen as the victim in a deadlock”, this issue might be caused by a deadlock.

     

    In order to solve this issue, we have to find out why this deadlock takes place.

     

    In order for troubleshooting deadlock, you could refer to the following link:

    http://blogs.msdn.com/b/bartd/archive/2006/09/09/deadlock-troubleshooting_2c00_-part-1.aspx?wa=wsignin1.0

    http://blogs.msdn.com/b/bartd/archive/tags/sql+deadlocks/default.aspx?wa=wsignin1.0

     

    Meanwhile if we want to enable replication agents for logging to output file in SQL Server, we could refer to this link: http://support.microsoft.com/kb/312292

     

    If anything is unclear, please let me know.


    Regards,
    Tom Li
    • Proposed as answer by Ivangelion.tw Thursday, March 31, 2011 7:42 AM
    Tuesday, August 24, 2010 8:35 AM