Severe error in Azure sql db using insert

Question

I get a severe error during insert statement on Azure sql db. So I can't check SQL Server Log. Until last week I didn't get this error so I have restored the db from then but no luck. Anyone have an idea or information on how to see the exact error?

Full error: A severe error occurred on the current command. The results, if any, should be discarded.

INSERT INTO [Staging].[table] ([att1],[att2]) SELECT [att1], [att2] FROM [Staging].[vw_table]".

Possible failure reasons: Problems with the query, \"ResultSet\" property not set correctly, parameters not set correctly, or connection not established correctly.

Connected to Master, you can try running select * from sys.event_log — David Browne - Microsoft
– David Browne - Microsoft, Commented Jan 9, 2019 at 19:40

Martijn Pieters · Accepted Answer · 2020-09-19 07:38:28Z

1

You should be getting error number 40540 that usually comes with that error message and for this error it is recommended a retry logic should be implemented as explained in this documentation.

It seems you are using Azure SQL Database to perform ETL workload usually related to the staging area of a data warehouse, and Azure SQL Database service may be throttled when using lower tiers for ETL and high I/O usage. Please run the following query and let’s try to find evidence about the Azure SQL Database being throttled.

SELECT *
FROM sys.dm_db_resource_stats
ORDER BY end_time DESC;

If you see avg_log_write_percent is close or equal 100% you need to scale up the tier of the database. You may consider scaling to premium tiers prior to running ETL processes which use premium storage. Scale down when the ETL process has finished.

What about the DTU graph on the portal? Please verify if consumption of resources is reaching the limits when errors appear.

One method to control throttling and high DTU usage is to perform inserts using batches as explained here.

edited Sep 19, 2020 at 7:38

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

answered Jan 8, 2019 at 19:09

Alberto Morillo

15.8k3 gold badges29 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

hatsjie Over a year ago

Thanks for the reply, I've done a lot of testing and indeed, scaling the database worked. But not everytime. What actually did the trick was setting a sequential load instead of a parallel load, ie in SSIS I now load table after table instead of loading 4 tables at once. Strange thing is that the resource stats showed no real overload on the database. I've opened a support ticket with Microsoft.

hatsjie · Accepted Answer · 2019-01-21 07:29:45Z

0

Reply from Microsoft for anyone who is interested:

Please find below the results of our investigation.

We noticed that the LOG% is reaching 100% very often, which could cause the errors that you are seeing.
Standard performance-tier databases have less IO than Premium Edition performance-tier databases.
As this workload is hitting maximum log rate for Standard S3/S4, it is recommended to use Premium. I would suggest to try a P1/P2 service tier and share with us the results.

As said before in the comment, when I changed to load in SSIS from parallel to sequential the error also disappeared.

edited Jan 21, 2019 at 7:29

answered Jan 21, 2019 at 6:51

hatsjie

18111 bronze badges

Collectives™ on Stack Overflow

Severe error in Azure sql db using insert

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related