Replication Deadlocks

As If


Replication is one of my least favorite things, and I go out of my way not to deal with it. I have had a few clients now that have run into problems with deadlocks arising from it doing Replication-y things.

If you use Plan Explorer to look at deadlocks, which you should because SSMS sucks at it, you’ll see stuff that looks like this:

i am deadlock

You’ll see deadlocks on things like LockMatchID, sys.sp_MSrepl_changestatus, and sp_MSrepl_addsubscription.

i am deadlock

You may also see weird looking ones like this on sp_addsubscription.

i am also deadlock

If you see deadlocks that involved Database Id 32767, and a negative object ID like -993696157, it’s going to be some weird replication stuff. That’s the Id of the Resource Database, which you can’t really get at without the DAC, or copying and attaching the files for it as a user database.

i too am deadlock

You may also see deadlocks on things like sp_replupdatechema coming from mssqlsystemresource.

Fixing It


The official line from Microsoft Support is that you can usually fix the deadlocks by running these commands:

EXEC sp_changepublication
  @publication = N'yourpublication',
  @property = N'allow_anonymous',
  @value = 0;
GO

EXEC sp_changepublication
  @publication = N'yourpublication',
  @property = N'immediate_sync',
  @value = 0;
GO

In practice, the clients I’ve had do this have had their Replication Deadlocks resolved. Hopefully if you’re hitting the same problems, you’ll find them useful.

Thanks for reading!



2 thoughts on “Replication Deadlocks

Leave a Reply

Your email address will not be published. Required fields are marked *