Microsoft.Data.SqlClient raised a timeout after 32 seconds when connecting to Azure SQL server

Question

I have a timely Azure Function wrote in .net core 8, and it uses Microsoft.Data.SqlClient, to connect to Azure SQL DB, here is a sample of the code:-

The main method:-

public async Task Run([TimerTrigger("0 0 5 * * *")] TimerInfo myTimer) //

{

try {

_logger.LogInformation($"Azure Function executed at: {DateTime.UtcNow}");

await UpsertDailyQualificationMilestoneToSqlServer(dailyQualificationMilestones);

_logger.LogInformation("Daily Qualification Milestone successfully processed.");

}

catch (Exception ex)

{

_logger.LogError($"Error: {ex.Message}");

}}

the Method to interact with SQL:-

private async Task UpsertDailyQualificationMilestoneToSqlServer(List<Milestone> Milestones)

{

using (IDbConnection conn = new SqlConnection(connectionString))

{

conn.Open();

foreach (var ar in Milestones)

{

string sql = @"

MERGE INTO DailyQualificationMilestone AS target

USING (SELECT @Id AS Id, @LogDate AS LogDate) AS source

ON target.Id = source.Id AND target.LogDate = source.LogDate

WHEN MATCHED THEN

UPDATE SET

Id = @Id,

LogDate = @LogDate,

LogType = @LogType,

LogActor = @LogActor,

Milestone = @Milestone,

Modified = GETDATE()

WHEN NOT MATCHED THEN

INSERT (Id, LogDate, LogType, LogActor, Milestone, Created, Modified)

VALUES (@Id, @LogDate, @LogType, @LogActor, @Milestone, GETDATE(), GETDATE());";

await conn.ExecuteAsync(sql, ar);

}

}

}

here is the connection string, which mentioned that the timeout is 120 seconds:-

Server=tcp:**.windows.net,1433;Authentication=Active Directory Managed Identity;Initial Catalog=*;Persist Security Info=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=120;

but yesterday when the azure function runs at 5 am UTC, it raised this exception:-

Error: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

after 32 seconds when the azure function started?? so what get timed out exactly ? as the timeout should happen after 120 seconds and not after 32 seconds. here the azure function logs:-

so the azure function started at 5:00:02 am , but timed out at 5:00:35.. so what is going on? the Milestones passed to the function contained around 15K items to be inserted or updated inside SQL..

Thanks

Answer 1

your error is query timeout, not connection.