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:
Go to Context.cs file that contains: public partial class YourEntity : DbContext
add the reference: using System.Data.Entity.SqlServer;
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:
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. * --->
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:
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 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:
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.
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
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.
I also got this error when I had not added my user in SQL to log in to the database and also granted some rights to select data.
Consider forexample a user called 'globoticket' and a database 'GloboTicket' Here is some T-SQL that can be run to give enough rights to read and write against the database (without extended permissions, such as altering schema in the form of e.g. dropping tables).
Note - we have already created the LOGIN itself in beforehand here and set the password for this user. We just switch to the target DB giving the error and here add the existing user to also this database via the 'Use GloboTicket' command.
use GloboTicket
go
CREATE USER globoticket FOR LOGIN globoticket
EXEC sp_addrolemember 'db_datareader', 'globoticket'
EXEC sp_addrolemember 'db_datawriter', 'globoticket'
Without this I get the following error in Swagger shown in my API project connecting to DB :
After running the SQL shown above we can read data with a 200 OK :
Key point here is that this error can be given for different error scenarios, since there are different answers here that has experienced this error. Looking at this, it seems that SQL Server reuses some error or are not helpful in showing the real error reason in some of the scenarios that will show this error message.
Note - double check under Security for all databases that your user has the necessary roles for the db and of course is listed to access this db. Double click the user and look at 'User mappings'. Verify that the checkmarks shows the necessary access to db.
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
.
.
.
I configured the the project to run in the development mode. But for some unknown reason, it always runs in the production mode.
So that means it uses the ConnectionStrings from appsettings.Production.json file. Because the ConnectionStrings in this file points to the production server and the production server is not reachable this error is genereted. Temporary rename this file and use the appsettings.Development.json it will solve it.
