Azure sql database related exceptions

Question

I have a database in azure which have standart s2 edition.In logs of my application I always see many exceptions such formats:

1.

System.Data.SqlClient.SqlException: The client was unable to establish a connection because of an error during connection initialization process before login. Possible causes include the following: the client tried to connect to an unsupported version of SQL Server; the server was too busy to accept new connections; or there was a resource limitation (insufficient memory or maximum allowed connections) on the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) ---> System.ComponentModel.Win32Exception: An existing connection was forcibly closed by the remote host

2.

System.Data.Entity.Core.EntityException: The underlying provider failed on Open. ---> System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached

3

System.Data.SqlClient.SqlException (0x80131904): The client was unable to establish a connection because of an error during connection initialization process before login. Possible causes include the following: the client tried to connect to an unsupported version of SQL Server; the server was too busy to accept new connections; or there was a resource limitation (insufficient memory or maximum allowed connections) on the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) ---> System.ComponentModel.Win32Exception (0x80004005): An existing connection was forcibly closed by the remote host.

I use SqlAzureExecutionStrategy so this exceptions are thrown after some number of retries.

I see different performance metrics on azure portla,but it seem they are ok.

How can I identify the problem?

How can I identify the problem? > Open a support case with Azure SQL DB. — Remus Rusanu
– Remus Rusanu, Commented Aug 22, 2016 at 13:44
Is your AzureSQL database located in the same Azure Region as your application code? Are you connecting to an Azure SQL database directly over the Internet (e.g. from desktop client software?). — Dai
– Dai, Commented Aug 22, 2016 at 13:50
@RemusRusanu,I don't have support plan,first I decided to ask stack — vborutenko
– vborutenko, Commented Aug 22, 2016 at 14:03

McGaz · Accepted Answer · 2016-08-22 14:04:37Z

I think that your database is under too heavy load, or you have some queries which are still running or not letting go of the connection.

I use this query to see what is running:

SELECT (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 , 
  ( (CASE WHEN statement_end_offset = -1 
     THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) 
     ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS sql_statement,
     s1.* FROM sys.dm_exec_requests s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 
ORDER BY 1

See if you have queries still running here or keep an eye on the CPU usage in the Azure portal.

The S2 databases aren't particularly good and it will throttle your requests so if you are doing lots of them (even small ones), it might be rejecting them.

Your retry strategy could also be making the problem worse, but throwing more requests at it when it has already been filled. You could try using an exponential back-off if this is the case.

Shannon Lowder · Accepted Answer · 2016-08-31 00:32:53Z

All three could be explained by your connection pooling design. Are you re-using your connections, or is every call to the database opening it's own connection? Are you closing connections at the end of each DBContext? Are you implementing any kind of caching layer to reduce the number of round trips to your database to a minimum?

Here's a way to see if it is an issue with your pooling. From the portal go to the database in question, look at the Resource utilization graph, then hit edit.

Then add Sessions percentage and workers percentage from the select list, and hit OK.

If your pooling is an issue, you'll find that your sessions and workers percentages are high, and may be pegged at 100% for periods. If you hit 100%, you can be denied new connections for up to 5 minutes until the current sessions and workers either finish, or get killed off.

Collectives™ on Stack Overflow

Azure sql database related exceptions

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related