Retry Logic for Azure SQL in .NET: Handling Transient Errors the Right Way

In the cloud, short interruptions are normal. Azure SQL Database occasionally moves a database to another node for maintenance, scaling or load balancing, and during those few seconds connections drop or new ones are refused. A well-built application shrugs these off with a short retry. A badly built one shows users an error page.

Retry logic for Azure SQL transient errors in .NET

Transient vs permanent errors

Only transient errors should be retried: ones that will probably succeed if you try again in a moment. Examples commonly treated as transient for Azure SQL include:

  • 40613: database is not currently available (often during a failover).
  • 40197: the service encountered an error processing your request.
  • 40501: the service is currently busy.
  • 49918, 49919, 49920: cannot process request because of too many operations or requests.
  • Connection-level failures such as timeouts while connecting or a forcibly closed connection.

Errors like a syntax error, a missing table, a permission denied or a constraint violation are not transient. Retrying them only delays the failure. The libraries below already know the standard transient list, which is one good reason to use them rather than hand-rolling your own.

Back off exponentially, with jitter

Retrying instantly in a tight loop makes things worse during an outage. Wait a little longer each time (for example 1, 2, 4, 8 seconds), add some randomness so thousands of clients do not retry at the same moment, and give up after a sensible total time.

EF Core: turn on the built-in strategy

services.AddDbContext<PayrollContext>(options =>
    options.UseSqlServer(connectionString, sql =>
        sql.EnableRetryOnFailure(
            maxRetryCount: 5,
            maxRetryDelay: TimeSpan.FromSeconds(30),
            errorNumbersToAdd: null)));

EF Core then retries queries and SaveChanges automatically on transient SQL errors.

Manual transactions need an execution strategy

With retries enabled, a transaction you open yourself must be wrapped so the whole unit is retried together; otherwise EF Core throws an exception telling you to do so:

var strategy = db.Database.CreateExecutionStrategy();

await strategy.ExecuteAsync(async () =>
{
    await using var tx = await db.Database.BeginTransactionAsync();
    db.PayrollRuns.Add(run);
    await db.SaveChangesAsync();
    await db.Database.ExecuteSqlRawAsync("EXEC dbo.PostPayrollRun @p0", run.Id);
    await tx.CommitAsync();
});

ADO.NET and Dapper: SqlClient retry logic

Microsoft.Data.SqlClient includes configurable retry logic that you can attach to a connection or command:

var options = new SqlRetryLogicOption
{
    NumberOfTries = 5,
    DeltaTime = TimeSpan.FromSeconds(1),
    MaxTimeInterval = TimeSpan.FromSeconds(20)
};
var provider = SqlConfigurableRetryFactory.CreateExponentialRetryProvider(options);

await using var conn = new SqlConnection(connectionString);
conn.RetryLogicProvider = provider;     // retries opening the connection
await conn.OpenAsync();

You can also set RetryLogicProvider on individual SqlCommand objects. Alternatively, many teams use a resilience library such as Polly to wrap data calls with the same retry policy as their HTTP calls.

Connection string settings

For idle connections that break, SqlClient can reconnect automatically using ConnectRetryCount and ConnectRetryInterval. A slightly longer Connect Timeout (such as 30 seconds) also helps during failovers:

Server=tcp:myserver.database.windows.net,1433;Database=Payroll;
Connect Timeout=30;ConnectRetryCount=3;ConnectRetryInterval=10;

Only retry what is safe to repeat

If a timeout happens after SQL Server committed your INSERT but before your app received the reply, a blind retry inserts a duplicate. Make writes idempotent: use unique keys, check-then-insert patterns, or a client-generated request ID so the second attempt is recognised and ignored.

Quick checklist

  • Retry only transient errors, using a library that knows them.
  • Exponential backoff with jitter, capped total time.
  • EF Core: EnableRetryOnFailure, and execution strategies for manual transactions.
  • ADO.NET/Dapper: SqlClient retry logic or Polly.
  • Make writes idempotent and log every retry so you can see patterns.

Related reading:

Comments

Post a Comment

Popular posts from this blog

How to Read a SQL Server Execution Plan (Beginner's Guide)

Image
When a query is slow, the execution plan tells you exactly what SQL Server did to run it: which indexes it used, how it joined tables and where the time went. Plans can look intimidating, but you only need a few habits to find the problem in most of them. Estimated vs actual plans SQL Server Management Studio (SSMS) can show two kinds of plan: Estimated plan (Ctrl+L): what SQL Server plans to do, without running the query. Useful for queries that are too slow or too risky to run. Actual plan (Ctrl+M, then run the query): the same plan plus what really happened, including actual row counts and warnings. Use this whenever you can. Turn on IO and time statistics at the same time so you have numbers to compare after each change: SET STATISTICS IO, TIME ON; -- run the query with "Include Actual Execution Plan" enabled SET STATISTICS IO, TIME OFF; Read from right to left Data flows from right to left. The operators on the far right read data from tables and indexes; each operato...
Read more

SARGable Queries in SQL Server: Why Functions in WHERE Slow You Down

Image
You add the perfect index, run the query, and SQL Server still scans the whole table. One of the most common reasons is a non-SARGable predicate: a WHERE clause written in a way that stops SQL Server from using the index to seek. The fix is usually a small rewrite. What SARGable means SARGable stands for S earch ARG ument- able . A predicate is SARGable when SQL Server can use it to jump straight to the matching rows in an index (an index seek). If you wrap the indexed column in a function or calculation, SQL Server can no longer search the index for a value; it has to calculate the expression for every row, which means a scan. The rule of thumb: leave the column bare on one side of the comparison , and do any calculation on the other side. Common non-SARGable patterns and their fixes Functions on date columns -- Not SARGable: YEAR() runs on every row WHERE YEAR(OrderDate) = 2026 -- SARGable: a range on the bare column WHERE OrderDate >= '2026-01-01' AND OrderDate < ...
Read more

SQL Server Statistics Explained: Why Your Query Plans Go Wrong

Image
When a query suddenly picks a terrible plan, the root cause is very often statistics . SQL Server's optimizer does not look at your data when it builds a plan; it looks at statistics about your data. If those are missing or out of date, it guesses wrong, and a wrong guess can mean a scan instead of a seek, the wrong join type or a memory spill. What statistics are A statistics object describes how values are distributed in one or more columns. SQL Server creates one automatically for every index, and, with AUTO_CREATE_STATISTICS on, for columns you filter or join on. The optimizer uses them to estimate how many rows each step will return, and those estimates drive almost every plan choice. How to read a statistics object DBCC SHOW_STATISTICS ('dbo.PayrollLine', 'IX_PayrollLine_Run_PayCode'); The output has three parts: Header: when the statistics were last updated, how many rows the table had, and how many rows were sampled . Density vector: how unique the column...
Read more