Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Replication DB - Dead lock issue Expand / Collapse
Author
Message
Posted Saturday, June 29, 2013 10:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 3:28 PM
Points: 182, Visits: 650
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,
Post #1468838
Posted Sunday, June 30, 2013 12:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 6:02 AM
Points: 198, Visits: 734
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.
Post #1468845
Posted Sunday, June 30, 2013 12:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 6:02 AM
Points: 198, Visits: 734
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.
Post #1468846
Posted Monday, July 1, 2013 8:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 3:28 PM
Points: 182, Visits: 650
Thanks.
We are subscriber as e getting from 3rd party vendor and it's happening during 1st week of the month.
Post #1469080
Posted Monday, July 1, 2013 8:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 6:02 AM
Points: 198, Visits: 734
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.
Post #1469082
Posted Monday, July 1, 2013 1:37 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 3:28 PM
Points: 182, Visits: 650
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.
Post #1469206
Posted Wednesday, July 10, 2013 5:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 3:28 PM
Points: 182, Visits: 650
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.
Post #1472067
Posted Wednesday, July 10, 2013 8:19 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 11:04 AM
Points: 594, Visits: 2,771
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



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1472178
Posted Monday, July 15, 2013 6:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 3:28 PM
Points: 182, Visits: 650
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.
Post #1473616
Posted Monday, July 15, 2013 7:34 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 5:42 PM
Points: 209, Visits: 641
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
Post #1473647
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse