Help get this topic noticed by sharing it on Twitter, Facebook, or email.

Deadlock occurs during refresh the local copy SF database

Hi
The version of DBAmp we use is 2.17.6 and we have scheduled SQL Agent jobs to refresh the SF local database (only about 40 objects) in every 15 minutes by using DBAmp.exe (we don't allow to use the xp_cmdshell) also, we have a scheduled job to replicate the local SF database every night.
The problem we have now is we have other scheduled jobs to access the local SF database and retrieve the data, process it and dump the file to shared network folder. sometimes, the scheduled jobs are run at the same time with the sync jobs and deadlock may occur. for some reason, we run SF_dropkeys before we run the DBAmp.exe to refresh the objects and recreate the keys after it is done. This drop key (Alter table) needs to have sch-M lock and the select query in other applications need the sch-S lock. Both sch-S and sch-M locks are incompatible and sometimes, deadlock happens.
We don't allow to use the xp-cmdshell, we create CLR user defined function to run the DBAmp.exe with bulkapi parameter to refresh the local SF database, process the delta and delete tables too.

What should we do for the deadlock because of SF_dropkeys? any suggestion? the sync job may take between 3 minutes to 6 minutes and sometimes it is about 9 minutes.

Thanks and let me know if you need more information,

Brian
1 person has
this question
+1
Reply