SQL Server connection closed by remote host

Question

I currently have a SQL Server hosted with Microsoft Azure on the basic tier. When calling db.SaveChanges() in Entity Framework Core I am getting the following error:

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

And

Win32Exception: An existing connection was forcibly closed by the remote host

Has someone possibly come across this before?

Might be because you have a lot of data and the query takes longer than the allowed time. Or maybe you execute multiple queries at the same time and the free tier limits you on concurrent requests. — Maxinoume
Probably, a previous call get Dispose(), please provide the code... — Alejo Ferrand

Alberto Morillo Alberto Morillo · Accepted Answer · 2019-03-07T20:05:16

If the connection is idle for minutes Azure SQL Database closes the connection. Please create a 'KeepAlive' method and call it every 5 minute to keep the connection open.

Sample keep alive method that reads a very small table.

private void KeepAlive()
{
    var dummy = _context.BankAccounts.AsNoTracking().ToList().Count;
    Console.WriteLine($@"{DateTime.Now} KeepAlive");
}

If you are processing a large number of records and then saving them, try to process and save them using batching.

Another thing you need to consider is to implement a retry logic to your code to handle transient errors as explained on this documentation.

One last thing, monitor DTU consumption on the portal and if you see DTU consumption reaching the limit then scale up to Standard tiers.

SQL Server connection closed by remote host

1 Answers