Post reply

Replication DB - Dead lock issue

  • June 29, 2013 at 10:32 pm

    #279367

    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,

  • June 30, 2013 at 12:30 am

    #1628530

    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.

  • June 30, 2013 at 12:34 am

    #1628531

    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.

  • July 1, 2013 at 8:15 am

    #1628723

    Thanks.

    We are subscriber as e getting from 3rd party vendor and it's happening during 1st week of the month.

  • July 1, 2013 at 8:18 am

    #1628725

    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.

  • July 1, 2013 at 1:37 pm

    #1628831

    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.

  • July 10, 2013 at 5:59 am

    #1631164

    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.

  • July 10, 2013 at 8:19 am

    #1631251

    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[/url] is an excellent read. Take a look at Stairway to SQL Server Replication - Level 10: Troubleshooting[/url] for more details about the Replication Monitor.

    "I cant 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."

    -- Itzik Ben-Gan 2001

  • July 15, 2013 at 6:26 am

    #1632435

    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.

  • July 15, 2013 at 7:34 am

    #1632462

    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

    DHeath

  • July 15, 2013 at 9:08 am

    #1632521

    poratips (7/15/2013)

    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.

    That does not sound like deadlocks, just good old-fashioned blocking.

    If the third party is making extensive modifications during the first week of each month, you may have to work with them to resolve this issue.

  • August 9, 2013 at 9:30 am

    #1640187

    Thanks.

    If i set up the Trace flag, and leave it to running for a month , will be ok or it will fill up the size?

  • August 9, 2013 at 12:19 pm

    #1640236

    Should not the be an issue unless you are generating hundreds of deadlocks.

  • August 9, 2013 at 1:34 pm

    #1640264

    Thanks.

  • August 10, 2013 at 8:45 am

    #1640364

    Thanks once again!

    I am laready Monitoring Activity Monitor and but lots of job is mainly coming from Replicated DB.

    Couple of job i have troubleshoot from other source job and improved performance adding right index and removed unnecessarry hint from the query.

Viewing 15 posts - 1 through 14 (of 14 total)

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