Error handling external file: 'IO request completed with an error. ERROR = 0x0000000C'. File/External table name

Question

This is an error I get when I query either from SSMS or as an error logged in with dataset Refresh in Power BI Premium capacity.

<ccon>DataSource.Error: <ccon>Microsoft SQL</ccon>: <ccon>Error handling external file: 'IO request completed with an error. ERROR = 0x0000000C'. File/External table name: 'CDM_Data/xxxxx/xxxxxx/xxxx.parquet'. Statement ID: {213396C4-3FF0-4E4E-A5D2-2AB5C955E9A4} | Query hash: 0x5B237160A2994733 | Distributed request ID: {07A62494-10ED-4AAC-AEA3-B3820989C1A3}. Total size of data scanned is 220 megabytes, total size of data moved is 108 megabytes, total size of data written is 0 megabytes.</ccon>. DataSourceKind = <ccon>SQL</ccon>. DataSourcePath = <ccon>xxxxxxxxxxx-ondemand.sql.azuresynapse.net;Service</ccon>. Message = <ccon>Error handling external file: 'IO request completed with an error. ERROR = 0x0000000C'. File/External table name: 'CDM_Data/CustomerCard/2023/06/CustomerCard202306.parquet'. Statement ID: {213396C4-3FF0-4E4E-A5D2-2AB5C955E9A4} | Query hash: 0x5B237160A2994733 | Distributed request ID: {07A62494-10ED-4AAC-AEA3-B3820989C1A3}. Total size of data scanned is 220 megabytes, total size of data moved is 108 megabytes, total size of data written is 0 megabytes.</ccon>. ErrorCode = <ccon>-2146232060</ccon>. Number = <ccon>15813</ccon>. Class = <ccon>16</ccon>. </ccon>. The exception was raised by the IDbCommand interface. Table: Service Folder.

• I am querying against a Serverless Pool
• The frequency varies, sometimes 2 out of 5 times in a day, today is was 5/5 times today and users are still looking at yesterdays data.
• A retry is a 50/50 chance of another fail of a similar error message (sometimes an IO from a different file - sometimes a Timeout Expired. )
• I experience this at least 3 times a week, on a bad day 5 times in a day.

The view is complex - There are at least 10 (base) views which are rowset parquet views against files in azure gen2 storage. The view which has the issues has a join across all 10. I am not aware of a query plan or other utility to perhaps troubleshoot the issue.

The underlying files may will be written to whilst the query is running, this is something out of my control.

0 votes

Answer 1

Hello @Dipak Bhudia, thanks for posting this question,

Based on the error message you provided, it seems that the issue is related to IO errors when querying external files in Azure Gen2 storage. This can happen if the files are being written to while the query is running, or if there are issues with the network connectivity or storage performance.

Did you try Increasing the timeout value?

I would suggest test with a different storage account!

You can try to monitor the storage account performance metrics to identify any issues.

please let us know if you need further help in this matter, happy to help!
I will try to convey this thread link to Pradeep!

Answer 2

Hi There - This is an issue that seems to occur for 24 hours and then self resolves and the system runs smooth again. But we are experiencing 24 hours of downtime when this happens. Timeout is not the issue, thats the symptom not the cause.

In my view, Azure Synapse is unstable and cannot reliably function as a datalakehouse solution with a guaranteed uptime. It seems every few weeks the environment just stops functioning with constant IO and Timeout issues and all Microsoft say is that I should avoid reading whilst writing (when this is not the case 95% of the time).

Please can someone help!!