During data loading, we faced SQL Error: 10054 , which was due to "Connection Forcibly Closed by Remote Server".
On further analysis, it was due to some server patch activity happening on our SQL Server environment. So, this error is bound to happen once in a while, when server goes down.
The solution for solving this problem is enabling retry mechanism. But, even retry immediately will not solve the issue. So, we have to use longRetry option in the ADF activity policy.
Originally, the activity policy was set as:
We changed the activity policy to:
The number of times slice will be attempted is: Retry times x No. of Long Retry times
So, in the above case, it will be 3 x 3 = 9 times, the activity slice will try to run.
We are keeping the longRetryInterval as 20 minutes, hoping that the server patch activity will get completed within 20 minutes and retry will be successful.
We can read more about it: https://github.com/twright-msft/azure-content/blob/master/articles/data-factory/data-factory-create-pipelines.md