SQLException with error code 0 and message "A severe error occurred on the current command. ..."

Question

Hello,

Once every few days I'm getting a SQLException saying something like "System.Data.SqlClient.SqlException (0x80131904): A severe error occurred on the current command. The results, if any, should be discarded. Operation cancelled by user." The SQL Error code is 0.

When I search for SQL error code 0, I can only find this piece of information:

https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlerror.number?view=dotnet-plat-ext-5.0#remarks

which states that it's one of four scenario's: (1) Read-only routing failure, (2) Server had severe error processing query, (3) Processed cancellation while parsing results or (4) Failed to create user instance.

I suspect that the 3rd scenario is the relevant one, because the SQLException also mentions "Operation cancelled by user". But at the same time I find that very strange. I wouldn't know who is cancelling what. Even though I do pass a cancellationToken, this token is only ever used when the application is already shutting down. I really don't think it's my code sending any cancellation, so I believe this cancellation is coming from somewhere else.

Then I started to look further. On the SQL Server I found a system_health log which, at the exact same time the SQLException occurred, contains a wait_info mentioning a LCK_M_IX. The code that causes the exception is executing a stored procedure at that time, which performs 2 UPDATE statements. So these could very well be waiting for the lock.

But why am I getting a SQL error 0 then? Why not a 1205 if it's a deadlock? Or a timeout code? Or another one that is lock related perhaps? Something less vague than error 0 "severe error" in any case. In my exception handling code I'm trying to distinguish between transient failures and non-transient ones so that I can decide whether to retry, or to crash the application. It doesn't seem logic that error 0 should be considered "transient" as it could mean anything (which I concluded after reading the aforementioned Microsoft page).

So how should I handle this situation? Or am I still not looking at the right places?

The code is similar to:

using (SqlConnection connection = SqlConnectionHelper.CreateAsyncConnection())   // Returns a new SqlConnection with the right connection string  
{  
   await connection.OpenAsync(cancellationToken).ConfigureAwait(false);  
  
   using (SqlTransaction transaction = connection.BeginTransaction())  
   using (SqlCommand command = CreateCommand(transaction))		// Creates a command with the right procedure name, parameter definitions, and a timeout of 300 seconds  
   {  
      foreach (var item in items)  
      {  
         command.Parameters["@Param1"].Value = item.Value1;  
         command.Parameters["@Param2"].Value = item.Value2 == null ? DBNull.Value : (object)item.Value2;  
  
         await command.ExecuteNonQueryAsync(cancellationToken);  
      }  
        
      transaction.Commit();  
   }  
}  

The exception occurs on the ExecuteNonQueryAsync line.

Some additional info:

the application is compiled for .NET Framework 4.6
the command timeout is set to 300 (5 minutes) but fails after 40 seconds or so
the ADO.NET async/await TAP methods are used
the SQL Server being called is version 2016 SP2-CU11.

I really hope someone can shed some light on things. We're having problems with this in our production environment.

Regards,
Stefan Adriaenssen

Answer 1

It's a client-side timeout, but not triggered by CommandTimeout. This is the error you get when your async command execution is aborted by a CancellationToken.

A severe error occurred on the current command. The results, if any, should be discarded. Operation cancelled by user." The SQL Error code is 0.

So whatever creates your CancellationToken is canceling the command. You can register a cancellation callback to examine the stack and see what code is causing the cancellation. eg

            cancellationToken.Register(() =>
                {
                    var st = new System.Diagnostics.StackTrace();
                    Console.WriteLine(st.ToString());
                });

Here's a simple example:

using Microsoft.Azure.Services.AppAuthentication;
using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace SqlClientTest
{

    class Program
    {

        static void Main(string[] args)
        {
            var ct = new CancellationTokenSource();

            var task = Run(ct.Token);

            task.Wait(20 * 1000);

            ct.Cancel();

            task.Wait();
        }

        static async Task Run(CancellationToken token)
        { 

            var constr = @"server=localhost;database=tempdb;Integrated Security=true";

            using (var con = new SqlConnection(constr))

            {
                con.Open();
                var cmd = new SqlCommand("waitfor delay '00:05:00'", con);
                try 
                {
                    await cmd.ExecuteNonQueryAsync(token);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex);
                }

            }
        }


    }

}

Answer 2

Hello @Stefan Adriaenssen

You might consider a) restarting the computer and/or the server when there is no processing b) use retry strategy like in this NuGet package.

Example

Retry.On<SqlException>(handle => (handle.Context.LastException as SqlException).Number == 1205).For(5).With(context =>  
{  
	// Code that might result in a SQL deadlock.  
});  

No matter which direction you take consider wrapping code in a try-catch with a rollback option.

Answer 3

The only really interesting component of the error message is the severity. For this error, the severity is 11 or 20. If it is 20, this means that a severe error occurred in SQL Server, and the connection was cut. Such errors are almost always due to bugs in SQL Server, and in this case, you can find a stack dump in SQL Server's errorlog.

If the severity is 11, the exception occurred on the client-side of things, and could be a bug in the client API. Although, my experience is that SQL Server is sending incorrect TDS packets, so the client calls it a day.

One way to monitor this is to run a Profiler trace while the program is running and capture the events Error:Exception and Error:UserMessage.

Answer 4

As Erland said, this is almost always a bug in SQL Server, although it can be caused by other things. The first thing to do is install the latest CU and test again. If it is a bug, it is likely the problem is already fixed.

https://support.microsoft.com/en-us/help/3177312/kb3177312-sql-server-2016-build-versions