'ErrorCode=SystemErrorOutOfMemory' when running Copy data activity in Fabric Pipeline

[cfccai]

Hello

I am using Fabric pipeline activity to copy data from on-prem SQL server database to Fabric lakehouse. It works good. So I schedule a hourly run.

What interesting is during this weekend, It run failed after several times success as below screenshot shown.

And after two times failed, it always run successfully again.

Checked the run log, it's error msg:

{
"copyDuration": 52,
"errors": [
{
"Code": 8000,
"Message": "ErrorCode=SystemErrorOutOfMemory,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A task failed with out of memory.,Source=Microsoft.DataTransfer.TransferTask,''Type=System.OutOfMemoryException,Message=Exception of type 'System.OutOfMemoryException' was thrown.,Source=Microsoft.DataTransfer.ClientLibrary,'",
"EventType": 0,
"Category": 5,
"Data": {
"OutOfMemory": "Exception of type 'System.OutOfMemoryException' was thrown."
},
"MsgId": null,
"ExceptionType": null,
"Source": null,
"StackTrace": null,
"InnerEventInfos": []
}
],
"usedParallelCopies": 1,
"executionDetails": [
{
"source": {
"type": "SqlServer"
},
"sink": {
"type": "Lakehouse"
},
"status": "Failed",
"start": "12/2/2024, 12:46:00 AM",
"duration": 52,
"usedParallelCopies": 1,
"profile": {
"queue": {
"status": "Completed",
"duration": 0
},
"transfer": {
"status": "Completed",
"duration": 3
}
},
"detailedDurations": {
"queuingDuration": 0,
"transferDuration": 3
}
}
],
"dataConsistencyVerification": {
"VerificationResult": "Unsupported"
}
}

Any idea / suggestions regarding this issue?

Thanks.

[Anonymous]

Hi @cfccai ,

From your description and your screenshot, it's easy to see that you mentioned 'ErrorCode=SystemErrorOutOfMemory'.

Here are some of my personal thoughts on your reported error:

1. Register and online self-hosted IR with powerful machine (high CPU/Memory) to read data from the big file through copy activity.

2. Use memory optimized + big size (for example, 48 cores...) cluster to read data from the big file through dataflow activity.

3. Split big file into small ones, then use copy or dataflow activity to read the folder.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cfccai]

I am wondering how this issue can happen. Since you will be noticed that it does not always fail. After fail, it can like recovery magically when next batch run. 

[Anonymous · Accepted solution]

Hi @cfccai ,

I think it could be some of the reasons below:

The error suggests that the task is running out of memory. This could be due to high memory usage on the server at certain times. Check if there are other processes running on the server that might be consuming a lot of memory during the times the pipeline fails.

The amount of data being transferred might vary, causing the pipeline to fail when the data volume is higher. Monitor the size of the data being transferred during each run to see if there's a correlation.

Check the performance metrics of your on-prem SQL server and the Fabric lakehouse during the times of failure.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FabianSchut]

Hi, what is your setup regarding the on-premise server? Do you have a VM with the data gateway installed or is the gateway directly installed on the machine where the SQL-server is running? In case of the VM, can you try to increase the memory? I also see that you run your copy activities in parallel. Can you try to decrease the number of parallel activities and see if that helps solve the issue?

[cfccai]

Adding the screenshots:  you will be noticed that checking the failed copy activity, only one or two tables copy job failed.