SQL 2012 transnational replication deadlock metadata and snapshot agent

• Rohan Joackhim

  Old Hand

  Points: 391

  More actions

  March 10, 2015 at 5:16 pm

  #316072

  Hi all,

  I have been getting a deadlock in production from the distribution database with a keylock (master.sys.sysxsrvs.cl).It has been escalated to a deadlock with metadata and snapshot agent.Its a bit complicated deadlock for me to understand what caused it.I have copied the error message I found in the distribution server SQL error log.I believe the message is a generic error message but in the SQL Sentry deadlock graph, it has

  mssqlsystemresource.sys.sp_dropserver,

  mssqlsystemresource.sys.sp_get_redirected_publisher ,

  mssqlsystemresource.sys.sp_hadr_drop_linked_server and mssqlsystemresource.sys.sp_validate_redirected_publisher as Owners.

  We have synchronous HADR setup with one read only node and transnational replication for single subscriber.Main database server act as the publisher and the Primary both.If anyone has experienced this please shed some light so I have a place to start the investigation.

  Date3/6/2015 4:00:15 PM

  LogSQL Server (Current - 3/9/2015 12:00:00 AM)

  Sourcespid116

  Message

  Replication-Replication Snapshot Subsystem: agent XXXXXX(publication) failed. The replication agent had encountered an exception.

  Source: Replication

  Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentException

  Exception Message: Validation failed for the publisher 'server' with error 21878 severity 16 message 'Un

  This was the error found in the replication monitor snapshot agent history. Soon as I restarted the snapshot agent for 2 failed batches replication is back in action.

  Message: Validation failed for the publisher 'Primary' with error 21878 severity 16 message 'Unable to create a linked server to use in contacting the remote publisher for original publisher 'Primary', publisher database 'DB01', and redirected publisher 'MAINDATABASE'. The command 'sys.sp_addlinkedserver' failed with Error '1205', Error Message 'Error 1205, Level 13, State 55, Message: Transaction (Process ID 152) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.'. '.

  Stack: at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.GetHadrRedirectedPublisherProcessResult(SqlDataReader dataReader)

  at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32 queryTimeout, CommandBehavior commandBehavior)

  at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate)

  at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.GetHadrRedirectedPublisher()

  at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()

  at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQL_REPL, Error number: MSSQL_REPL54172)

  Get help: http://help/MSSQL_REPL54172