1

I'm encountering a problem setting up transactional replication between SQL Server 2017 and an Azure SQL database on a high throughput system.

When trying to add any of the bigger, higher traffic tables to a publication as a new article a deadlock occurs. It seems to be happening as the snapshot completes and it stops the objects for the new table being created on the subscriber Azure database, but the snapshot appears to complete successfully. Following that the log reader service tries to send data to the subscriber and it errors as the table and procedures for the new article have not been created.

The replication has been set up successfully on a development environment that mirrors production. We have also been able to add the majority of tables to a publication in the production environment. It is these last few tables on the production system that are problematic.

I got a deadlock graph, but I don't understand it. In summary, there are two Object Locks. One takes a Schema Modification lock on the SPID that is the replication publication. Schema Stability locks are then taken between multiple other SPIDs and the two Object Locks. Finally the replication publication SPID attempts to take a Schema Modification lock on the other Object Lock. At that point the deadlock occurs and 3 of the other SPIDs are killed.

I've searched online but can't find anything that seems to reference a similar situation.

The business is a 24 hour operation and any downtime to try and complete the setup would not be ideal.

Has anyone experienced anything similar, or does anyone have any advice on best practices for this type of situation?

Thanks

1
  • 2
    Adding the deadlock graph to your post would be helpful.
    – J.D.
    Aug 5 at 19:36

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.