September 18, 2008 at 9:30 am
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
September 19, 2008 at 4:01 am
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
September 25, 2008 at 3:49 am
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.
October 1, 2008 at 2:58 am
Any progress on this? I'd be interested.
Mike
October 1, 2008 at 3:13 am
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
May 22, 2009 at 4:26 am
April 23, 2012 at 2:10 pm
i do have simillar problem.
DBCC DBREINDEX(MSreplication_subscriptions) helps me to resolve it immidiately.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply