CODEWITHSUNDEEP
azuret-sqlazure-sql-database
hatsjie
hatsjie

Reputation: 181

Severe error in Azure sql db using insert

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.

Upvotes: 2

Views: 1166

Answers (2)

Alberto Morillo
Alberto Morillo

Reputation: 15698

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.

Upvotes: 1

hatsjie
hatsjie

Reputation: 181

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.

Upvotes: 0

Related Questions