Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Replication DB - Dead lock issue


Replication DB - Dead lock issue

Author
Message
poratips
poratips
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 653
Hi,
We have One Database which is a Replication Db, sometimes during beginning of the month, lot of user activities going and we having Dead lock issue.
Any one has idea how to prevent the dead lock in Replication DB?
We have to manually kill the user session one by one but if someone having same kind of issue and have any idea?


Thanks,
WolfgangE
WolfgangE
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 Visits: 777
Dealing with deadlocks is a very very very extensive topic.
For the beginning, try to get detailled information about each deadlock (for example use trace flag 1222).
Then have a look at each one and try to solve it whith the appropriate means. As each case is specific I cannot give you more information now.
WolfgangE
WolfgangE
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 Visits: 777
one more thing: what is a replication DB for you? Published database or subscribed? Whatever, I don't think it's something specific with replication that causes your deadlocks. Replication is just reading and inserting data like other processes.
poratips
poratips
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 653
Thanks.
We are subscriber as e getting from 3rd party vendor and it's happening during 1st week of the month.
WolfgangE
WolfgangE
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 Visits: 777
Then activate trace flag 1222 for the whole server.
When the next deadlock occurs, have a look at the sql server log file. You will see the deadlock information quite exactly.
poratips
poratips
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 653
Thanks.
Just a additional information:

It's set up as a Transactional Snapshot and we are subscriber and We don't have control for Publisher as it's from 3rd party vendor and they control everything.

Thanks.
poratips
poratips
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 653
Thanks once again.
After observing, looks like it's more waiting as user has to wait for longer periods and sometime it extending longer and longer.
Alan.B
Alan.B
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2236 Visits: 7419
The best way to resolve deadlocks is adding the trace flag as Wolfgang mentioned. That said, when you are dealing with Replication I strongly suggest you become familiar with the Replication Monitor. Especially when dealing with Transactional Replication (I think that's what you meant by "Transactional Snaphot") and Merge Replication. I could not live without it.

Sebastian Meine's Stairway to Replication is an excellent read. Take a look at Stairway to SQL Server Replication - Level 10: Troubleshooting for more details about the Replication Monitor.

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
poratips
poratips
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 653
Thanks Alan.
Somehow we have every few minutes replication is running but sometimes it's locking and users have to wait and wait so sometimes we have to kill the session for user.
DHeath
DHeath
SSC Veteran
SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)

Group: General Forum Members
Points: 223 Visits: 650
As mentioned ...use the trace available and see if you can also use Activity monitor and when the dead lock happens try to see what code is running BEFORE you kill the process....most of the deadlocks a you know is due to the code doing the blocking while the application is doing its "thing". Once you have the code that is causing the issues(blocking and dead locks) you may be able to send that to the vendors and have the makes the needed adjustments. Just a thought and good luck.

Dheath
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search