Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
5,378 questions
Synapse serverless CETAS: Error handling external file: 'bad allocation'.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(185.6, 42%, 27%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESS%3C/text%3E%3C/svg%3E)
Steven SV
0
Reputation points
I have a stored procedure that executes a CETAS statement. This SP is called from a synapse pipeline (nightjob) which first deletes the storage folder. Since a couple of days it generates the following error:
Execution fail against sql server. Please contact SQL Server team if you need further support. Sql error number: 15813. Error Message: Error handling external file: 'bad allocation'. File/External table name: 'https://xxx/C643F951-ADAC-432F-9F0A-CC36BA8335C2_14266_0-8.parquet'. Statement ID: {261B9084-788F-4510-8221-C6B47673F3A8} | Query hash: 0x796CCE149A415EC7 | Distributed request ID: {C643F951-ADAC-432F-9F0A-CC36BA8335C2}. Total size of data scanned is 954 megabytes, total size of data moved is 4580 megabytes, total size of data written is 1 megabytes. The statement has been terminated.
This applies to our TEST synapse environment. In the PROD environment (which in fact uses the same source data) the nightjob runs smoothly.
Does anyone have experience with this 'bad allocation' error?
Any help would be much appreciated!
Azure Synapse Analytics
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(288, 61%, 37%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EP%3C/text%3E%3C/svg%3E)
phemanth 15,765 Reputation points Microsoft External Staff Moderator2023-09-07T10:34:08.5366667+00:00 Hi @Steven SV ,
Thanks for approaching Microsoft Q&A.
From the issue it seems there is some error related to memory allocation to the process. Can you please confirm whether the configurations of workspaces are same for both test and prod?
If so please, try with some lesser data and see how it behaves. If the issue persists, please do let us know. Thank you.
-
Steven SV 0 Reputation points
2023-09-07T11:26:55.7733333+00:00 Hi @phemanth ,
Yes both workspace have the same configuration.
I did some testing by limiting the number of rows and/or output columns: no problems with less data. Also no problems by making the table less wide. Interestingly the external table has 332 columns. The CETAS works when I only include the first 200 columns, but it also works when I only include the last 200 columns. So it is not related to a specific column.
As you suggest it is probably memory allocation releated. Do you know what checks I can perform?
-
phemanth 15,765 Reputation points Microsoft External Staff Moderator
2023-09-08T11:53:31.2333333+00:00 Hi @Steven SV ,
Thanks for responding to our comment.
Can I know where you are running it whether it is from dedicated SQL or serverless SQL?
And also, can you confirm us if any other request or process is running and utilizing memory?
Could you please check these details, and could you please share screenshots if possible?
Thank You.
-
phemanth 15,765 Reputation points Microsoft External Staff Moderator
2023-09-11T05:26:54.4933333+00:00 Hi @Steven SV We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
-
Steven SV 0 Reputation points
2023-09-11T09:41:06.9533333+00:00 Hi @phemanth the problem still exists. Since we are running it on a Synapse Serverless environment I don't know where to list the utilized memory. From synapse studio I can see that there are no running request. There are also no pipelines running (we only make contact with the synapse DB through synpase pipelines)
Is there a kind query that I can use to list the utilized memory?
-
phemanth 15,765 Reputation points Microsoft External Staff Moderator
2023-09-12T08:36:31.0766667+00:00 Hi, @Steven SV
we suggest that you can create external table by splitting the columns of the table into two tables and u can union them so that you can run successfully.
and check the SQL requests of both TEST and PROD environment and check whether the readings are the same and share the readings with us such as submitted time, ended time, running time, data processed per each query, submitter and query status (only completed and failed).
-
phemanth 15,765 Reputation points Microsoft External Staff Moderator
2023-09-13T09:18:58.81+00:00 Hi, @Steven SV
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
-
phemanth 15,765 Reputation points Microsoft External Staff Moderator
2023-09-14T09:17:35.75+00:00 We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Sign in to comment
Sign in to answer