Error when connect database continuously

When I am querying from database in continuous looping, after some time I get an error :

> An exception has been raised that is likely due to a transient > failure. If you are connecting to a SQL Azure database consider using SqlAzureExecutionStrategy.

Normally it is working fine.

If you are using EF Core configure retry on failure for resilient connections :

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
	optionsBuilder.UseSqlServer("your_connection_string", builder =>
		{
			builder.EnableRetryOnFailure(5, TimeSpan.FromSeconds(10), null);
		});
	base.OnConfiguring(optionsBuilder);
}

When connecting to a SQL Database you have to account for transient connection failures. These connection failures can happen for example when updates are rolled out, hardware fails etc. The error you see indicates that one of these things happened which is why your connection was dropped. Enabling an Execution Strategy as suggested by Anbuj should solve the issue.

Enable an execution strategy as mentioned here : https://msdn.microsoft.com/en-us/data/dn456835.aspx . When designing for Azure SQL DB, you have to design for transient connection failures, since back-end updates, hardware failures, load balancing can cause intermittent failures at times.

I could see that nobody put the solution in case of Entity Framework, and not EF core. The easiest way to implement SqlAzureExecutionStrategy is:

1. Go to Context.cs file that contains: public partial class YourEntity : DbContext

2. add the reference: using System.Data.Entity.SqlServer;

3. add another class at the end of the file containing the following code:

   public MyConfiguration()
   {
       SetExecutionStrategy("System.Data.SqlClient", () => new SqlAzureExecutionStrategy());
       SetDefaultConnectionFactory(new LocalDbConnectionFactory("mssqllocaldb"));
   }
   

You may refer to this documentation for more information.

I am posting this answer as I face lot of issue while researching the answer of the issue. Below is the detailed error message I was getting:

Dividing the errors in the parts as the error was too long:

> 1. System.Data.Entity.Core.EntityException: ***An exception has been raised that is likely due to a transient failure. If you are > connecting to a SQL Azure database consider using > SqlAzureExecutionStrategy. *** ---> > > > > > 2. System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner > exception for details. > ---> System.Data.SqlClient.SqlException: Resource ID : 1. The request limit for the database is 30 and has been reached. See > 'http://go.microsoft.com/fwlink/?LinkId=267637'; for assistance. at > System.Data.SqlClient.SqlConnection.OnError(SqlException exception, > Boolean breakConnection, Action`1 wrapCloseInAction)

After research I found that it was related to the limits of Azure SQL Database maximum logins. I was using 'Basic' service tire and max concurrent users can login with that is 30.

Azure has pricing tiers that have quite dramatic differences in performance. To achieve that, they throttle a lot of performance metrics, e.g. CPU power, requests per minute, etc.

This means that if you're pushing over your tier, your requests will start getting queued up as the CPU power / volume of requests is too high to process. This results in timeouts and then the request limit grows as requests wait to be processed. Eventually, it gets to the point where the database essentially goes down.

My experience is that the lower database levels, such as S0 and S1, are under-powered and shouldn't be used for anything other than development or very basic sites.

There are some great tools in the Azure portal that allow you to debug what is going on with your database, such as the CPU graphs, index advisor and query performance insights.

Here are the related links:

• https://blogs.technet.microsoft.com/latam/2015/06/01/how-to-deal-with-the-limits-of-azure-sql-database-maximum-logins/

  https://docs.microsoft.com/en-us/azure/sql-database/sql-database-dtu-resource-limits-single-databases

Conclusion:

Part 1:Enable an execution strategy as mentioned here: https://msdn.microsoft.com/en-us/data/dn456835.aspx .

• Part 2: You need to upgrade the subscription in the Azure(if price permits).

Thanks.

I get this error when the login I am trying to connect to the database with does not have an associated user in the database.

This could be because of TLS setting, .net 4.5 framework don't support tls 1.2 by default and new SQL db is not compatible with older tls ver . so either disable tls 1.0,1.1 in your machine or update to .net 4.6.2

I would like to give my contribution to those who, like me, have encountered this error even though they are not in the situation of having to interact with a database on Azure or on another platform in the cloud.

The first goal is to get to the exact origin of the problem; like me many will have collided with the exception text and a mile-long stack trace string. It was useful for me to process the matryoshkas of the InnerExceptions to get to the real message that the database provider issues before closing the connection (which was active at the time of the message!). Alternatively, if possible, it is sufficient to monitor the transactions towards the database from an external tool that allows you to check any errors connected to the TSQL operations in progress (eg SQL Server Profiler).

In my case the scenario was this: 2 instances of the same program (it is a Windows service) that insert records inside a table. Two peculiarities:

• for Windows services, such as for Form or WPF desktop applications, the life cycle of the DbContext is usually longer and it is possible to link it to the Form being processed rather than keep it active for the entire duration of the project having the foresight to periodically refresh it to clear the cache valued up to that moment;
• the target table has its own auto incremental (integer) key field

In this scenario, concurrently, all services's instances try to write the same table and each write operation performed with EF6 produces a query that has a very particular select within it for retrieving and enhancing the key field it represents identity. Something like this:

INSERT [dbo].[People]([Name]) VALUES (@0)
SELECT [Id], [SomeId]
FROM [dbo].[People]
WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()

My code was:

People entry = new People();
entry.name = "ABCD";
_ctx.Set<People>().Add(entry);
await _ctx.SaveChangesAsync();

This type of writing leads to a situation of concurrency between the two processes (expecially when table have about 5M records), which induce the SQL engine to resolve one request and cancel the other. The interpretation of the calling program is precisely "An exception has been raised that is likely due to a transient failure. If you are connecting to a SQL Azure database consider using SqlAzureExecutionStrategy."

To get around the problem I had to make a waiver on the recovery of the incremental id assigned to the new record, treating the table as a stack and reducing the write operation to a direct insert operation using:

await _ctx.Database.ExecuteSqlCommandAsync("INSERT INTO ....");

Alternatively, it is possible to refine the operation with two write operations that do not involve the EF TSQL parser and also retrieve the identifier assigned to the last added record.

In my case this bug was caused by disposing context in the middle of query execution because of missing await.

If your database is local to let's say a WebAPI, sometimes you must provide data source=localhost not an IP address. We have the situation where we are using some VPS and without setting data source to a localhost gives this error. So, if anybody else has experienced this, it could help him.

The solution for this problem is through the suggested answer, use SetExecutionStrategy() to turn on a retry policy. Also make sure to derive from the class DbConfiguration so that Entity Framework can execute the method automatically.

You also want to make sure that your connection resiliency really works through setting up an command interception which creates connection errors so that you can confirm it works.

More about how to handle transient connection errors

I got this error when try to get sp result with ef core 2.2 then I try to use this code in begin of my sp and issue was solved.

ALTER procedure [dbo].[getusers]
@param int
As
Set transaction isolation level read uncommitted
.
.

.

For me this error happens when dotnet try to use the ConnectionStrings from appsettings.Production.json file. Because the ConnectionStrings in this file points to the production server. Rename or delete this file when you are in development mode it will solve it.