Edit

Configure retry logic in SqlClient

Applies to: .NET Framework .NET .NET Standard

Download ADO.NET

This article shows how to create an exponential retry provider and assign it to a SqlConnection. The provider retries the driver's built-in list of transient connection errors and attempts to open the connection up to five times.

Prerequisites

  • Microsoft.Data.SqlClient 3.0 or a later version.
  • A connection string for SQL Server, Azure SQL Database, Azure SQL Managed Instance, or SQL database in Microsoft Fabric.

Configure a connection retry provider

  1. Define the retry options. Leave TransientErrors as null so the provider uses the built-in transient error list.

    // Define the retry logic parameters
var options = new SqlRetryLogicOption()
{
    // Tries 5 times before throwing an exception
    NumberOfTries = 5,
    // Preferred gap time to delay before retry
    DeltaTime = TimeSpan.FromSeconds(1),
    // Maximum gap time for each delay time before retry
    MaxTimeInterval = TimeSpan.FromSeconds(20)
};

  2. Create a provider from the options.

    // Create a retry logic provider
SqlRetryLogicBaseProvider provider = SqlConfigurableRetryFactory.CreateExponentialRetryProvider(options);

  3. Assign the provider before opening the connection.

    // Assumes that connection is a valid SqlConnection object 
// Set the retry logic provider on the connection instance
connection.RetryLogicProvider = provider;
// Establishing the connection will retry if a transient failure occurs.
connection.Open();

NumberOfTries = 5 allows one initial attempt and up to four retries. The exponential provider increases the interval between attempts and adds random jitter. MaxTimeInterval caps each individual delay, not the total elapsed time for all attempts.

Configure command retries

Create a separate provider and assign it to SqlCommand.RetryLogicProvider. Set SqlRetryLogicOption.AuthorizedSqlCondition to a predicate that returns true only for commands your application can safely repeat.

Caution

A built-in provider doesn't retry a command when the connection has an active transaction. If a transient failure invalidates a transaction, roll back and retry the entire transaction. Don't retry only the failing statement.

Setting SqlRetryLogicOption.TransientErrors replaces the driver's built-in error list. To extend the baseline without losing the defaults, see Extend the built-in transient error list.

Related content

  • Last updated on