lundi 18 septembre 2017

SQL Server keeps getting deadlocked when running an SSIS

I have a scheduled SSIS package that runs from a dedicated SSIS server we have, and makes connections to a SQL Server 2012. We also have an applications server that hosts an app that also connects to this SQL Server. The issue that we have been running into is a deadlock. The deadlock message says that it is from a processID coming from the application server.

Occasionally I can use activity monitor on the SQL server and kill the process coming from the apps server, while at the same time starting the SSIS, and the job will run and complete. This seems to rely on timing however, and requires SSIS server login to have a higher processID that the apps server login. Just for reference, once I kill a login coming from the apps server it takes about 2-3 seconds for the login to show back up in activity monitor.

However on occasion it requires a full re-boot of the SQL Server, as it did this morning. Once I reboot I no longer need to kill a process from the apps server to get the SSIS to run and complete, even though in activity monitor on the SQL server I can see the apps server login.

I have combed over the stored proc used by the SSIS and everything seems to work so don't think it's the USP itself that is locking the process, and as stated before sometimes killing process unrelated to the SSIS works.

I'm hoping others here have seen this issue and can provide some guidance as to what can be done to keep this deadlock from happening.

Thanks.

Aucun commentaire:

Enregistrer un commentaire