Microsoft.Data.SqlClient.SqlException (0x80131904): The connection is broken and recovery is not possible. The connection is marked by the client driver as unrecoverable. No attempt was made to restore the connection.

Question

User888038912 posted

Hello,

I hope I'm in the right forum ;), if not please kindly just move the post.

I'm currently developing an .net core 3.1.2 console app running in a Linux docker container under Azure.

It uses a S0 DB instance in Azure to keep it's data (Later going to up-scale).

I have very large batch transactions (100k insert's,deletes,updates taking up to over 1 hour) and experience to following error sometimes on the application side:

Microsoft.Data.SqlClient.SqlException (0x80131904):
The connection is broken and recovery is not possible.
The connection is marked by the client driver as unrecoverable. No attempt was made to restore the connection.

My SQL connection string looks as follows:

Server=tcp:xxxxxx.database.windows.net,1433;Initial Catalog=xxxxxxx;Persist Security Info=False;User ID=xxxxxx;Password=xxxxxxx;MultipleActiveResultSets=True;Encrypt=True;TrustServerCertificate=False;Connection Timeout=900;Min Pool Size=0;Max Pool Size=300;Connection Lifetime=0;ConnectRetryCount=90;ConnectRetryInterval=10

I'm using the following package version of Microsoft.Data.SQLClient: 2.0.0-preview2.20084.1

(By the way: When I switch back to the old System.Data.SQLClient the problem seems to be not appearing)

The error related stack trace is:

2020-03-31T18:09:02.622478644Z [33m ---> Microsoft.Data.SqlClient.SqlException (0x80131904): The connection is broken and recovery is not possible. The connection is marked by the client driver as unrecoverable. No attempt was made to restore the connection.[0m
2020-03-31T18:09:02.622484944Z [33m at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)[0m
2020-03-31T18:09:02.622489644Z [33m at Microsoft.Data.SqlClient.SqlConnection.ValidateAndReconnect(Action beforeDisconnect, Int32 timeout)[0m
2020-03-31T18:09:02.622513143Z [33m at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)[0m
2020-03-31T18:09:02.622518443Z [33m at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)[0m
2020-03-31T18:09:02.622523543Z [33m at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)[0m
2020-03-31T18:09:02.622528443Z [33m at Microsoft.Data.SqlClient.SqlCommand.BeginExecuteNonQueryInternal(CommandBehavior behavior, AsyncCallback callback, Object stateObject, Int32 timeout, Boolean inRetry, Boolean asyncWrite)[0m
2020-03-31T18:09:02.622533043Z [33m at Microsoft.Data.SqlClient.SqlCommand.BeginExecuteNonQueryAsync(AsyncCallback callback, Object stateObject)[0m
2020-03-31T18:09:02.622537543Z [33m at System.Threading.Tasks.TaskFactory`1.FromAsyncImpl(Func`3 beginMethod, Func`2 endFunction, Action`1 endAction, Object state, TaskCreationOptions creationOptions)[0m
2020-03-31T18:09:02.622542843Z [33m at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQueryAsync(CancellationToken cancellationToken)[0m

I hope someone could help me out here. Otherwise I will need to use the old System.Data.SQLClient.

I'm just wondering how the connection can break (and is not recoverable) since app side and DB side are both on Azure.

Thx a lot.

Best Regards,

Markus

Answer 1

User-474980206 posted

It’s called preview for a reason. Not sure why processing only 100k records takes an hour. I’d thought a minute or two would be plenty. You should probably be doing set operations.

Answer 2

User888038912 posted

Thanks, I tested also with the latest stable version but result is the same.

FYI: I'm using Dapper as persistence framework.

For now I added some more debugging output in my code.

Maybe the network is not so stable between the app and the DB environment (even both on Azure in the same region).

Are there any special DB connection string parameters available to force a reconnect. The ones in my string seems to be ignored.

Thanks again.

Answer 3

User-474980206 posted

polly Is the typical library for resilient connections. 

https://github.com/App-vNext/Polly

Polly and dapper

https://hyr.mn/dapper-and-polly/

Answer 4

User888038912 posted

Thx a million. This was exactly the information I needed.

FYI, I created 2 wrapper classes around DbConnection and the DapperExtensions I'm using as I would not like to update xxx code calls where dapper was used.

Still a bit tricky as I'm using mostly transactions and in case of Transient Failures I will try to re-run the whole transaction stack.

But I think I can handle.

I will update this post once done and tested.

Thx again.

Update:

Even after using the retry I still got the issue.

It turned out that I seems to be affected by the following  bug

https://github.com/dotnet/SqlClient/issues/422

I turned off MARS for the moment and my problems are gone.

Nevertheless it's good to have also the retries in place.