SQL Server Performance Forum – Threads Archive

Dynamic snapshot jobs deadlocking

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

---

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.

---

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.
Kenny Lam
Software Developer
http://www.icomply.com.au

---

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.

---

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

---

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.

---

]]>