![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(233.6, 41%, 32%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EPD%3C/text%3E%3C/svg%3E)
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,611 questions
Implement a keep-alive mechanism in your application to periodically query the database, preventing it from going idle. You can use a background task to accomplish this.
Example: Keep-Alive Task in ASP.NET Core
public class DatabaseKeepAliveService : IHostedService, IDisposable
{
private Timer _timer;
private readonly string _connectionString;
public DatabaseKeepAliveService(IConfiguration configuration)
{
_connectionString = configuration.GetConnectionString("DefaultConnection");
}
public Task StartAsync(CancellationToken cancellationToken)
{
// Set up a timer to trigger the keep-alive query every 10 minutes
_timer = new Timer(KeepAlive, null, TimeSpan.Zero, TimeSpan.FromMinutes(10));
return Task.CompletedTask;
}
private void KeepAlive(object state)
{
using (var connection = new SqlConnection(_connectionString))
{
try
{
connection.Open();
using (var command = new SqlCommand("SELECT 1", connection))
{
command.ExecuteScalar();
}
}
catch (Exception ex)
{
// Handle exceptions as needed
}
}
}
public Task StopAsync(CancellationToken cancellationToken)
{
_timer?.Change(Timeout.Infinite, 0);
return Task.CompletedTask;
}
public void Dispose()
{
_timer?.Dispose();
}
}
Register the Service
In your Startup.cs or Program.cs, register the DatabaseKeepAliveService:
public void ConfigureServices(IServiceCollection services)
{
services.AddHostedService<DatabaseKeepAliveService>();
// Other services
}
Although you mentioned setting the connection timeout to 60 seconds, ensure that it's correctly set in the connection string. However, this alone might not resolve the issue if the database takes longer to wake up.
Consider upgrading to a higher pricing tier that does not have aggressive idle timeout settings. The free tier and lower pricing tiers may have limitations that cause the database to go idle more frequently.
Implement retry logic in your application to handle transient failures. This can help mitigate the impact of the initial connection failure when the database is waking up.
Example: Retry Logic with Polly
public class DatabaseService
{
private readonly string _connectionString;
public DatabaseService(IConfiguration configuration)
{
_connectionString = configuration.GetConnectionString("DefaultConnection");
}
public async Task ExecuteQueryAsync()
{
var policy = Policy
.Handle<SqlException>()
.WaitAndRetryAsync(3, retryAttempt => TimeSpan.FromSeconds(Math.Pow(2, retryAttempt)));
await policy.ExecuteAsync(async () =>
{
using (var connection = new SqlConnection(_connectionString))
{
await connection.OpenAsync();
using (var command = new SqlCommand("YOUR SQL QUERY", connection))
{
await command.ExecuteNonQueryAsync();
}
}
});
}
}
By implementing these strategies, you can mitigate the issue of the database going idle and improve the reliability of your application's database connections.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(211.20000000000002, 61%, 30%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBS%3C/text%3E%3C/svg%3E)
