Deadlocking ON MSreplication_subscriptions

Question

Post reply

Deadlocking ON MSreplication_subscriptions

Wedge78

Ten Centuries

Points: 1102
More actions
September 18, 2008 at 9:30 am

#198251

Hi

I am having issues with deadlocking. I am running a trace on profiler and receiving a deadlock graph.

Using the associated object id and the following query

SELECT object_name(object_id)

FROM sys.partitions

WHERE partition_id = 72057594066173952

it gives me MSReplication_subscriptions as the culprit??

I have no idea how to resolve this issue. It is only replication spids that are being deadlocked. Surely replication should not allow this.

I have also made sure that no articles are duplicated across the subscriptions but am at a loss

Any advice would be appreciated

thanks

Chris

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

dbaker-620086 SSCrazy Points: 2336 More actions · Answer 1

the distribution db is used by various agents

LogReader (writes)

Distrib Agent (read+write)

Distribution clean up: distribution (delete)

plus other players

Agent history clean up: distribution

Replication agents checkup

Expired subscription clean up

where these run every 10 minutes

if you have big transactions (begin tran; INSERT x50K; UPDATE x50K; DELETE x50K; commit) or lotsa subs, distribution db can be a hotspot (and these 10-minute jobs can run for a lot longer that 10 minutes, but this is OK).

You could try to offset these secondary jobs so they don't all start at the same time (eg exactly on the hour), so they might compete less.

You should size your distribution db to handle the peak workload, and this may entail pre-allocation or ensuring AutoGrow maxima are high enough (otherwise activity will block badly), and increment is decent (eg +100MB and not the 1MB or 10% halfwit defaults MS provides in model).

Apart from such DBA-plumbing suggestions above, you should look to what the apps are actually doing (big/long transactions?), network bandwidth (are many DA's competing to reach many [slow] subs), and efficiency of dataflows (round-trips on transactional vs bulk-insert for snapshots).

Get your infrastructure folk to check for bottlenecks (CPU,RAM,I/O,network) and try some PerfMon, Profiler etc

HTH

Dick

Mike_D Hall of Fame Points: 3295 More actions · Answer 2

Check the contents of the table and its index. This has given me headaches in the past. If you are using SQL2000 or earlier in its default "mode" then the publication column will be blank and this column is the first column in the composite index on the table. The full index is

publication, publisher_db, publisher, subscription_type, transaction_timestamp

drop it and recreate it as something like

publisher, publisher_db, publication, subscription_type, transaction_timestamp

The default index is useless unless you are using a dedicated distribution agent for each publication (SQL2005 default so index is okay). If you use SQL2000 or earlier and have multiple publications on a publisher to a single database on a subscriber then by default one distribution agent will handle the data so there is nothing sensible which can be put in the "publication" column of MSReplication_Subscriptions. It will set blank and so the updates table scan. Changing the index worked for me.

Mike_D Hall of Fame Points: 3295 More actions · Answer 3

Mike_D

Hall of Fame

Points: 3295

October 1, 2008 at 2:58 am

#879356

Any progress on this? I'd be interested.

Mike

Wedge78 Ten Centuries Points: 1102 More actions · Answer 4

Hi guys

Have let this slide onto the back burner. But It has now been resolved. The stats on the MSReplication_subscriptions

table were out of wack. There were 7 records in it but if you looked in properties of the table it said it had 4 billlion.Update stats didnt help so I dropped and recreated the PK and it was fine after that. Replication obviously wasnt happy with the 4 billion rows