SQL Server Performance

Dynamic snapshot jobs deadlocking

Discussion in 'Performance Tuning for SQL Server Replication' started by kthl, May 1, 2007.

  1. kthl New Member

    We have an application that uses SQL Server 2000 merge replication with
    dynamic snapshots.

    At one of our clients, the dynamic snapshot jobs are set up in such a way
    that they run nightly from 10pm onwards in five minute increments. That is,
    job 1 will run at 10:00, job 2 at 10:05, job 3 at 10:10 and so on.

    With this particular client, some of the dynamic snapshot jobs are becoming
    deadlocked and so not all of them have completed successfully by the next
    morning.

    The hosting company for the client has sent us the log for one of the
    dynamic snapshot jobs and the reason for it failing was that 'The process
    could not bulk copy out of table 'cont1B520C4C11BA465085DB60491555D0BA'.

    As we do not have direct access to the client's database server, are there
    any tips anyone can offer as to how we diagnose what is going wrong with
    these jobs? We have obtained a copy of the client's database but so far have
    been unsuccessful at reproducing this situation on the development server in
    our office.


    Kenny Lam
    Software Developer
    http://www.icomply.com.au
  2. satya Moderator

    Any chance can you get a copy of that database on daily basis and restore it on your environment to manage the show?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  3. kthl New Member

    Hi Satya, unfortunately that's not feasible as our client's hosting company would charge them a fee to continually do this everyday.

    The issue is occurring only on the client's server as we have not been able to reproduce it on other servers thus far.



    quote:Originally posted by satya

    Any chance can you get a copy of that database on daily basis and restore it on your environment to manage the show?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

    Kenny Lam
    Software Developer
    http://www.icomply.com.au
  4. MohammedU New Member

    Do you see any dead locking information in error logs or just guessing?

    Most of the times when the disk is out of space when you are running snapshot you get "The process could not bulk copy out of table...." error.

    As them to check the disk space during the time of snapshot...

    If it is dead lock then enable Trace flage 1204 and see what is causing the dead locks...

    If you don't have the access then you can't do other than suggesting them to do something...if they are willing to do...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  5. kthl New Member

    Thanks for the suggestions Mohammed.

    I received a log from the hosting company and the "The process could not bulk copy out of table..." message was in the failed job. The hosting company says it was locked jobs.

    I will ask them to use trace flag if the jobs deadlock again.



    Kenny Lam
    Software Developer
    http://www.icomply.com.au
  6. satya Moderator

    Blocking is an issue for merge replication primarily when other processes are accessing data at the same time as the merge process. Poorly optimized partition queries might hold locks and block other processes, or other processes might hold locks that block the merge process. Depending on the database settings (that is, SET LOCKTIMEOUT) the blocked process might result in an error.

    Merge replication requires excessive locks and holds them longer and the merge replication process is more prone to deadlock failures. Again, this may be a natural side effect of the implementation of merge replication, but using efficient partitions reduces the potential for deadlocks.

    Relevant kBAhttp://support.microsoft.com/kb/925678 in this case.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Share This Page