How to avoid returning partial data from SQL Server in .NET application?

Question

We got ticket from client which is reporting that sometimes get partial data from query. In simple word query is a query on view:

SELECT column1, column2, column3, column4, column5, column6 ... column50 
from [dbo].[view]

View return around 3 million records. On server we have some rules that kill query after 30 minutes of running. In normal situation this query run around 5 minutes but in cases of big workload this query run more than 30 minutes. In this situation client could get two kind of errors:

• A killed query that returns

“Msg 596, Level 21 Cannot continue the execution because the session is in the kill state”

which causes the application import node to fail and therefor is the “ideal” situation, because they are not relying on a workflow with partial input dataset.

• An scenario related to killing process with a

“Msg 0, Level 20 A severe error occurred on the current command”

which from an ODBC perspective looks the same as a successful run and therefore we end with a partial dataset - this is the most dangerous situation.

Do you know maybe how to avoid this kind of issue ?

Answer 1

You left off the most important part of that second error (emphasis mine):

A severe error occurred on the current command. The results, if any, should be discarded.

Whatever the "application import node" is, it needs to deal with this error by discarding the resultset - and potentially undoing any work it did on the results that had been streamed in so far.

Any other solution would be a workaround, and likely an unsafe one.

It's important to note that, while KILLing the spid can cause this error, so can reading corrupt pages from disk. If you see this error unexpectedly, you should run DBCC CHECKDB to make sure your data is safe.

...are you sure that connection via ODBC can catch this kind of error?

I wrote a small program in C# that creates an ODBC connection (using the System.Data.OdbcConnection class) and starts streaming results from a long-running query:

using System.Data.Odbc;

try
{
    var connection = new OdbcConnection("DSN=SQL2019");
    var command = new OdbcCommand(@"SELECT * FROM dbo.Posts", connection);

    connection.Open();
    var reader = command.ExecuteReader();

    while (reader.Read())
    {
        var id = reader.GetInt32(reader.GetOrdinal("Id"));
        Console.WriteLine(id);
    }
}
catch (Exception e)
{
    Console.WriteLine(e);
    throw;
}

Each time I ran the program, I waited a few seconds and then issued a KILL command on the spid that was running my query.

I tried this with four different ODBC drivers on my computer, and encountered errors from each one:

The errors I got were as follows:

---

ODBC Driver 17 for SQL Server

System.Data.Odbc.OdbcException (0x80131937): ERROR [08S01] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: An existing connection was forcibly closed by the remote host.

ERROR [08S01] [Microsoft][ODBC Driver 17 for SQL Server]Communication link failure

SQL Server

System.InvalidOperationException: The connection has been disabled.
---> System.Data.Odbc.OdbcException (0x80131937): ERROR [01000] [Microsoft][ODBC SQL Server Driver][DBMSLPCN]ConnectionRead (recv()).
ERROR [08S01] [Microsoft][ODBC SQL Server Driver][DBMSLPCN]General network error. Check your network documentation.

SQL Server Native Client 11.0

System.Data.Odbc.OdbcException (0x80131937): ERROR [08S01] [Microsoft][SQL Server Native Client 11.0]TCP Provider: An existing connection was forcibly closed by the remote host.

ERROR [08S01] [Microsoft][SQL Server Native Client 11.0]Communication link failure

SQL Server Native Client RDA 11.0

Same as previous

---

All of that to say, the application should get an error on the connection that got killed. And it should have logic for dealing with the fact that the results might not have been complete when the query was killed.

Answer 2

We had a similar problem, except our initial query was killed by a network outage. That, in turn, corrupted one of our indexes that was busy rebuilding. The result? We finally figured out that, if you query a certain table, SQL goes into kill state. I manually rebuilt each index, then tested the table after each rebuild. Finally found the index causing a problem, I deleted it, recreated it, and all went smoothly from there.

The SQL logs, event viewer entries, SSRS logs, were al near useless on this one...