A background service may run continuously for hours or days. During that time, it connects to a database, processes records, waits for new work, and repeats the same operations.
This creates a connection management problem that is easy to overlook.
A database connection that works when a service starts may no longer be usable several hours later. The database might restart, a network device might close an idle TCP connection, a database failover might occur, or an authentication token might expire.
If the application keeps a database connection open for the entire lifetime of its background service, it risks holding a connection that is no longer healthy.
The next database operation may fail even though the application itself is still running normally.
This is particularly important in ASP.NET Core applications that use BackgroundService, hosted workers, Entity Framework Core, or Microsoft.Data.SqlClient.
The solution is not to repeatedly restart the worker or disable connection pooling. It is to manage connection lifetimes correctly, allow the database driver to recover where supported, and create new database contexts when necessary.
Why a Database Connection Can Become Dead
A database connection represents an active communication channel between an application and a database server.
Once established, it can be used to execute queries, update records, and manage transactions.
However, the connection depends on several external components remaining available.
For example, consider a background service that connects to SQL Server and waits for work.
While the service is idle, one of the following events may occur:
The database server restarts.
A database failover moves the workload to another server.
A firewall or network device closes an idle connection.
A cloud database pauses or resumes.
An authentication credential or access token expires.
A network interruption breaks the underlying TCP connection.
The application may not immediately know that the connection has become unusable.
A simplified scenario looks like this:
Background Service Starts
|
v
Database Connection Opens
|
v
Process Records
|
v
Service Waits for New Work
|
v
Database or Network Changes
|
v
Existing Connection Becomes Invalid
|
v
Service Tries Another Query
|
v
Database Operation Fails
The important detail is that the background service can remain healthy while its database connection is no longer usable.
This is why a long-lived worker should not depend on one permanently open database connection.
Understand the Difference Between an Open Connection and a Pooled Connection
Before implementing recovery logic, it is important to understand how ADO.NET connection pooling works.
Microsoft.Data.SqlClient uses connection pooling by default.
When an application opens a SqlConnection, the driver attempts to obtain a suitable physical connection from a pool.
When the application closes or disposes the SqlConnection, the physical connection is normally returned to the pool rather than immediately disconnected.
For example:
await using var connection =
new SqlConnection(connectionString);
await connection.OpenAsync(cancellationToken);
await using var command = connection.CreateCommand();
command.CommandText = "SELECT COUNT(*) FROM Orders";
var result = await command.ExecuteScalarAsync(
cancellationToken);
When the method finishes, the logical connection is disposed.
The underlying physical connection can then be reused by another operation.
This distinction is important because disposing a SqlConnection does not mean that the application loses the performance benefits of connection pooling.
The driver manages the physical connections and their reuse.
A background service should therefore create and dispose logical connections around database operations rather than keeping one SqlConnection as a field for the entire lifetime of the worker.
Why Connection Pools Can Contain Stale Connections
Connection pooling improves performance by avoiding a complete network connection and authentication process for every database operation.
However, the driver does not necessarily send a validation query to the database every time it reuses a pooled connection.
Doing so would add a network round trip to every connection checkout and reduce the performance benefits of pooling.
As a result, a connection that was healthy when returned to the pool may become disconnected while it is idle.
When the application later checks out that connection and attempts to communicate with the database, the failure can be detected.
The driver can then discard the broken connection.
This means a connection failure on the first operation after a long idle period does not automatically indicate a connection leak or an application bug.
It may simply be the first time the application discovers that an idle connection was disconnected by the database or network.
The correct response is to allow the driver to recover where supported and retry only operations that are safe to repeat.
Avoid Keeping SqlConnection as a Singleton Field
One common mistake is creating a database connection when a background service starts and storing it as a field.
For example:
public class OrderWorker : BackgroundService
{
private readonly SqlConnection _connection;
public OrderWorker(string connectionString)
{
_connection = new SqlConnection(connectionString);
}
protected override async Task ExecuteAsync(
CancellationToken stoppingToken)
{
await _connection.OpenAsync(stoppingToken);
while (!stoppingToken.IsCancellationRequested)
{
await ProcessOrdersAsync(
_connection,
stoppingToken);
await Task.Delay(
TimeSpan.FromSeconds(30),
stoppingToken);
}
}
}
This implementation has several problems.
The connection remains associated with the worker for its entire lifetime.
If the database restarts or the network connection is interrupted, the worker may continue trying to use the same connection.
The implementation also makes connection recovery more complicated because the service must manage the connection state, disposal, and reconnection logic itself.
A better approach is to create a logical connection for each database operation.
public class OrderRepository
{
private readonly string _connectionString;
public OrderRepository(string connectionString)
{
_connectionString = connectionString;
}
public async Task ProcessOrdersAsync(
CancellationToken cancellationToken)
{
await using var connection =
new SqlConnection(_connectionString);
await connection.OpenAsync(cancellationToken);
await using var command = connection.CreateCommand();
command.CommandText = """
SELECT TOP (100) Id, Status
FROM Orders
WHERE Status = 'Pending'
ORDER BY Id
""";
await using var reader =
await command.ExecuteReaderAsync(
cancellationToken);
while (await reader.ReadAsync(cancellationToken))
{
var orderId = reader.GetInt32(0);
// Process the retrieved record.
}
}
}
The connection is opened when the repository needs it and disposed when the operation finishes.
The next execution obtains a connection from the pool or creates a new physical connection if necessary.
This allows the driver to manage connection reuse without forcing the background worker to retain an old connection.
Let Microsoft.Data.SqlClient Recover Broken Idle Connections
Microsoft.Data.SqlClient provides connection recovery options for qualifying transient failures.
Two connection string properties are particularly relevant:
Connect Retry Count
Connect Retry Interval
Connect Retry Count determines how many retry attempts are allowed for qualifying failures during initial connection establishment and recovery of a broken idle connection.
Connect Retry Interval determines the delay between subsequent attempts.
For example:
Server=my-server;
Database=OrdersDb;
Authentication=Active Directory Default;
Connect Timeout=30;
Connect Retry Count=3;
Connect Retry Interval=5;
Pooling=True;
The application can configure these settings through SqlConnectionStringBuilder:
using Microsoft.Data.SqlClient;
var builder = new SqlConnectionStringBuilder(
connectionString)
{
ConnectTimeout = 30,
ConnectRetryCount = 3,
ConnectRetryInterval = 5,
Pooling = true
};
var configuredConnectionString = builder.ConnectionString;
The application should create the connection string once during startup and reuse the same configuration.
These settings help the driver recover from qualifying connection failures.
However, there is an important limitation: Connect Retry Count and Connect Retry Interval do not automatically retry a SQL command that fails while it is executing.
For example, if an application starts an UPDATE statement and the network connection disappears during execution, the driver cannot always determine whether the operation completed.
The application needs a separate retry strategy for commands and business operations.
This distinction prevents developers from assuming that connection recovery settings make every database operation automatically reliable.
Use Connection Idle Timeout When Supported
Another useful option is Connection Idle Timeout.
Starting with Microsoft.Data.SqlClient 7.1.0, applications can configure how long a pooled connection can remain idle before it becomes eligible for eviction.
For example:
Server=my-server;
Database=OrdersDb;
Pooling=True;
Connection Idle Timeout=120;
This configures a 120-second idle timeout.
However, there is an important configuration detail.
Idle-timeout enforcement is opt-in and requires the following AppContext switch to be set to false:
AppContext.SetSwitch(
"Switch.Microsoft.Data.SqlClient.UseLegacyIdleTimeoutBehavior",
false);
Set the switch during application startup, before database connections are created.
The corresponding connection string property can also be configured through SqlConnectionStringBuilder:
var builder = new SqlConnectionStringBuilder(
connectionString)
{
IdleTimeout = 120
};
The connection pool then uses the configured idle-time behavior to remove connections that have remained unused for the specified period.
This can be useful for background workers that frequently enter long idle periods.
However, Connection Idle Timeout does not validate an actively checked-out connection, and it does not replace command retry logic.
It is also version-specific. Applications using earlier Microsoft.Data.SqlClient versions should not assume that this setting is available.
Choose the timeout according to the database and network environment rather than setting an unnecessarily aggressive value. Closing connections too frequently can increase physical connection creation, authentication, and network overhead.
Use Load Balance Timeout for Connection Age Management
Connection Idle Timeout and Load Balance Timeout solve different problems.
Connection Idle Timeout concerns how long a connection remains idle in the pool.
Load Balance Timeout concerns the age of a connection when it is returned to the pool.
For example:
Server=my-server;
Database=OrdersDb;
Pooling=True;
Load Balance Timeout=300;
This configuration tells the pool to discard a connection when it is returned after exceeding the configured age.
The value is measured in seconds.
This can help gradually replace older physical connections in applications where database infrastructure changes or connections should not remain active indefinitely.
However, Load Balance Timeout does not mean that every connection is forcibly disconnected after five minutes, even if it is currently executing a query.
The connection is evaluated when it returns to the pool.
Do not configure both idle and age-based limits without understanding their combined effect.
Frequent connection turnover can increase physical connection creation and authentication overhead.
For many applications, the default pool behavior is sufficient. Add custom lifetime settings only when the workload or infrastructure requires them.
Use a New DbContext When a Database Operation Fails
Applications using Entity Framework Core should avoid keeping one DbContext alive for the entire lifetime of a background worker.
A DbContext represents a unit of work. It also maintains tracked entities and other internal state.
When a database operation fails because of a connection problem, creating a new context is generally safer than continuing to use the existing one.
Register a context factory:
builder.Services.AddDbContextFactory<ApplicationDbContext>(
options =>
options.UseSqlServer(
connectionString,
sqlOptions =>
sqlOptions.EnableRetryOnFailure(
maxRetryCount: 5,
maxRetryDelay: TimeSpan.FromSeconds(10),
errorNumbersToAdd: null)));
builder.Services.AddHostedService<OrderWorker>();
The background service can create a fresh context for each database operation:
public class OrderWorker : BackgroundService
{
private readonly IDbContextFactory<ApplicationDbContext>
_contextFactory;
private readonly ILogger<OrderWorker> _logger;
public OrderWorker(
IDbContextFactory<ApplicationDbContext> contextFactory,
ILogger<OrderWorker> logger)
{
_contextFactory = contextFactory;
_logger = logger;
}
protected override async Task ExecuteAsync(
CancellationToken stoppingToken)
{
while (!stoppingToken.IsCancellationRequested)
{
try
{
await using var context =
await _contextFactory.CreateDbContextAsync(
stoppingToken);
var pendingOrders = await context.Orders
.AsNoTracking()
.Where(x => x.Status == "Pending")
.OrderBy(x => x.Id)
.Take(100)
.ToListAsync(stoppingToken);
foreach (var order in pendingOrders)
{
_logger.LogInformation(
"Found pending order {OrderId}",
order.Id);
}
}
catch (OperationCanceledException)
when (stoppingToken.IsCancellationRequested)
{
break;
}
catch (Exception ex)
{
_logger.LogError(
ex,
"Database operation failed in order worker.");
}
await Task.Delay(
TimeSpan.FromSeconds(30),
stoppingToken);
}
}
}
The context is disposed at the end of each iteration.
The SQL Server execution strategy can retry qualifying transient database operations, while the context factory allows the application to create a new unit of work when needed.
These are complementary techniques.
The execution strategy handles supported transient failures. The context factory controls the lifetime of the EF Core context.
One important consideration is that enabling retries can cause EF Core to buffer query results internally. Large queries may therefore require additional memory.
For very large datasets, evaluate the effect of retry-enabled execution strategies and consider suitable batching or streaming approaches.
Handle Database Failures With a Controlled Retry Policy
A background worker should not stop permanently because of a temporary database interruption.
At the same time, it should not retry continuously without any delay.
An uncontrolled retry loop can increase database load during an outage and make recovery more difficult.
A better approach uses bounded retries with exponential backoff.
For example:
public async Task ExecuteWithRetryAsync(
Func<CancellationToken, Task> operation,
CancellationToken cancellationToken)
{
const int maxAttempts = 5;
for (var attempt = 1; attempt <= maxAttempts; attempt++)
{
try
{
await operation(cancellationToken);
return;
}
catch (SqlException ex) when (
IsTransientDatabaseError(ex) &&
attempt < maxAttempts)
{
var delaySeconds = Math.Min(
Math.Pow(2, attempt),
30);
_logger.LogWarning(
ex,
"Database operation failed. Retry {Attempt} of {MaxAttempts} in {DelaySeconds} seconds.",
attempt,
maxAttempts,
delaySeconds);
await Task.Delay(
TimeSpan.FromSeconds(delaySeconds),
cancellationToken);
}
}
}
The IsTransientDatabaseError method must use an application-specific policy or the database provider's supported transient-error classification.
Do not assume that every SqlException is temporary.
Authentication failures, invalid SQL statements, missing tables, and permission errors generally require a configuration or code correction rather than repeated retries.
Also, avoid placing a second retry loop around an operation that is already being retried by EF Core without calculating the combined retry count and delay.
Nested retry policies can multiply the number of attempts and increase recovery time unexpectedly.
For database operations that modify data, retries must also account for idempotency.
If the application loses its connection during transaction commit, it may not know whether the database committed the transaction successfully.
Before repeating the operation, verify its outcome or use a design that safely handles duplicate execution.
Understand When to Clear a Connection Pool
Microsoft.Data.SqlClient provides two APIs for clearing connection pools:
ClearPool clears the pool associated with a specific SqlConnection configuration.
ClearAllPools clears all Microsoft.Data.SqlClient pools in the current process or application domain.
For example:
using Microsoft.Data.SqlClient;
await using var connection =
new SqlConnection(connectionString);
SqlConnection.ClearPool(connection);
These methods are useful in specific situations, such as a known credential change or a configuration boundary where existing pooled connections should no longer be reused.
The driver also clears affected pools automatically when it recognizes certain fatal errors, such as a failover.
However, manually clearing every pool whenever a database operation fails is not a good recovery strategy.
It forces future operations to create new physical connections and authenticate again.
If every background worker clears its pool during an outage, the application can create a large number of simultaneous login attempts when the database becomes available.
This can increase pressure on the database during an already difficult recovery period.
Use pool-clearing APIs only when there is a specific reason to invalidate existing pooled connections.
Avoid Connection Pool Fragmentation
A background application may appear to have a connection leak even when every connection is properly disposed.
One possible reason is connection pool fragmentation.
Microsoft.Data.SqlClient creates separate pools for different connection configurations.
For example, these two connection strings can create separate pools because their text differs:
Server=my-server;Database=OrdersDb;Pooling=True
Database=OrdersDb;Server=my-server;Pooling=True
Even though the effective settings are equivalent, the connection string text is different.
Other causes of pool fragmentation include creating new credential objects for every operation or using different access token values directly in connection objects.
A better approach is to build one canonical connection string and reuse it.
var builder = new SqlConnectionStringBuilder(
connectionString)
{
ApplicationName = "Orders.BackgroundWorker",
Pooling = true,
MaxPoolSize = 50
};
var configuredConnectionString = builder.ConnectionString;
Keep this configuration stable throughout the application's lifetime.
For Microsoft Entra authentication, use supported authentication modes or a stable AccessTokenCallback rather than creating a new token-based pool for every refreshed access token.
This is especially important for long-running services that authenticate continuously.
Monitor Connection Pool Behavior
A background worker can recover from connection failures, but teams still need visibility into what is happening.
Microsoft.Data.SqlClient provides diagnostic counters that help investigate connection pool behavior.
Useful measurements include:
Active pooled connections.
Free pooled connections.
Number of active pools.
Physical connections created.
Physical connections closed.
Logical connection checkouts and returns.
Reclaimed connections.
Connections in stasis.
These metrics help distinguish between several different problems.
For example, a growing number of physical connections may indicate pool fragmentation or frequent connection turnover.
A high number of active connections with very few free connections may indicate excessive concurrency or connections that are not being returned.
Connection timeouts may indicate pool exhaustion, slow database operations, blocked transactions, or database capacity limitations.
Application logs should also record:
Background job identifier.
Database operation name.
Connection open duration.
Query execution duration.
Retry attempt.
Exception type and error number.
Time since the previous successful database operation.
For SQL Server, administrators can inspect the active sessions:
SELECT
session_id,
status,
login_name,
host_name,
program_name,
last_request_start_time,
last_request_end_time
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
ORDER BY last_request_start_time DESC;
Using a stable Application Name such as Orders.BackgroundWorker makes it easier to distinguish worker connections from ordinary API traffic.
Avoid logging credentials, access tokens, or complete connection strings.
Production Checklist for Long Running Background Services
A reliable background service should have a clear connection lifecycle and a recovery strategy.
Use the following checklist when reviewing an existing worker:
Do not keep a SqlConnection open as a singleton field.
Open logical connections only when database work is required.
Dispose connections, commands, readers, and transactions on every execution path.
Keep one canonical connection string per workload.
Use connection pooling unless there is a specific reason not to.
Use Connect Retry Count and Connect Retry Interval for supported connection recovery scenarios.
Enable EF Core retry strategies where appropriate.
Create fresh DbContext instances for separate units of work.
Use Connection Idle Timeout only when the SqlClient version and application configuration support it.
Keep transactions short and avoid holding them during external API calls.
Use bounded retries and make data-changing operations safe to repeat.
Monitor active, free, and reclaimed connections.
Test recovery after database restarts, failovers, and network interruptions.
A particularly useful test is to start the worker, allow it to remain idle, restart the database or interrupt the network, and then verify that the next database operation recovers as expected.
This reveals problems that ordinary unit tests may never detect.
Summary
Long-running background services can encounter database connection failures even when their application code has not changed. Database restarts, network interruptions, failovers, idle connection cleanup, and authentication changes can all make an existing physical connection unusable.
The most important design decision is to avoid keeping a database connection open for the entire lifetime of a background worker.
Microsoft.Data.SqlClient connection pooling allows applications to dispose logical connections while still reusing physical connections. Its connection recovery options help with qualifying failures involving initial connection establishment and broken idle connections. EF Core execution strategies provide another layer of resilience for supported transient database operations.
Newer Microsoft.Data.SqlClient versions also provide configurable idle connection expiration, while pool-clearing APIs are available for specific credential or configuration changes.
These mechanisms solve different problems and should not be treated as interchangeable.
A reliable background service uses short-lived database operations, controlled retries, fresh DbContext instances, safe transaction boundaries, and connection pool monitoring. This allows the worker to recover from temporary database interruptions without depending on a connection that may have become invalid hours earlier.
Join the conversation! Your thoughts help the community grow.