9

I get the following error while trying to create a clustered index

The statement has been terminated.
Msg 596, Level 21, State 1, Line 0
Cannot continue the execution because the session is in the kill state.

Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

The index is:

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT

BEGIN TRANSACTION
GO

CREATE CLUSTERED INDEX IX_CO_DES_INPUT 
ON dbo.CO_DES_INPUT(DESIGN_ID, PRODUCT_INPUT_NUM, INPUT_NAME)
          WITH(STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
               ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

ALTER TABLE dbo.CO_DES_INPUT 
  SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

I am using Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64) Standard Edition (64-bit) on Windows NT 6.3 (Build 9600: )

I have run

DBCC CheckDB ('concept-test') WITH NO_INFOMSGS, ALL_ERRORMSGS  

and it found no problems

I am afraid that my database is corrupt since I am getting this error. How do I correct my issue and get this index put on the table?

  • Have you looked in the Sql Server Error Log for any additional information? – Scott Hodgin Nov 7 '16 at 12:58
  • Is it possible that there are a high number of duplicate rows on the column set that you are putting the index on? If you attempt to create the same index, but nonclustered, do you get the same error? – Nic Nov 7 '16 at 14:18
  • I do get the same error when I go to add a non clustered index on the same table. I tried adding the non clustered first. I got this error. So I thought that I might have index corruption and tried to drop all indexes and recreate them. That is when I got the error message I originally posted. I might add that the Idera SQL Integrity check did find corruption on the indexes for this table. Now the question is, how do I fix it. – user2904235 Nov 8 '16 at 12:57
  • Would it be possible that your operation fail when it finds out that there´s a constraint violation or something like that? Please take a look to this: http://stackoverflow.com/questions/34518205/c-sharp-cmd-executescalar-cannot-continue-the-execution-because-the-session – Victor Barajas Jan 8 '17 at 20:40
3

This can be caused by Corruption of Index,

The Best way to manage this is Drop the Old Index which is corrupt.

DROP INDEX dbo.CO_DES_INPUT.IX_CO_DES_INPUT 

And than Run the query in questions, it should work fine if all records are unique. in the given columns.

2

Something I've unwittingly found: When you are disabling / rebuilding indexes on an indexed view, you will get this same error if you try to rebuild one ore more of the non-clustered indexes prior to building the clustered or use REBUILD ALL.

3

In a database configured for Always On Availability Groups, you may also encounter this behavior if the Availability Group is in SYNCHRONOUS mode and secondaries have gone offline or are suspended. This is a side-effect experienced by application servers. Thanks,

2

I always get this error when I have a running query, using an Always On AG Listener, using one of the AG databases and there is a failover:

.Net SqlClient Data Provider: Msg 596, Level 21, State 1, Line 0 Cannot continue the execution because the session is in the kill state. .Net SqlClient Data Provider: Msg 0, Level 20, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.

Very easy to reproduce! Of course, there might be other reasons but this is the one particular instance I am getting this type of error...

3

Just a pointer re: SQL Server 2017 (where I was hitting this error) and some versions of 2016 to potentially save others some time:

Before SQL Server 2017 CU8 (also 2016 SP1 CU9 and 2016 SP2 CU1), there was an issue where partitioned tables with incremental stats would generate this error if the DML operation caused an automatic stats update.

Minimal KB page is here:

https://support.microsoft.com/en-us/help/4163478/fix-access-violation-when-incremental-statistics-automatically-updated

Installing a later CU than those listed above fixes the issue.

0

I can reproduce this issue in a fairly small C# program that uses Asynchronous Connections, but I am not sure 100% why. Here is my repro program in case others want to give it a try - I suspect there is a series of things that have to line up perfectly for this to happen:

  • Connection pooling enabled
  • Using impersonation in the connection pool, disallowing reverting that impersonation context in the connection pool
void Main()
{
    var impersonateMyself = System.Security.Principal.WindowsIdentity.GetCurrent().Name;

    var testCommand = "SELECT TOP 1 * FROM sys.objects";
    var calls = Enumerable.Repeat(
        $@"{testCommand};",
        10
    );
    var impersonatedCalls = Enumerable.Repeat(
        $@"EXECUTE AS LOGIN = '{impersonateMyself} WITH NO REVERT';  {testCommand}; REVERT;",
        10
    );
    Dictionary<string, object> dict = new Dictionary<string, object>()
    {
    };

    // Scenario 1: Impersonated Calls, With connection pooling -- will randomly fail
    Parallel.ForEach(
        impersonatedCalls,
        c => new SqlAsync("Data Source=devsql2;Initial Catalog=Test;Integrated Security=true;Max Pool Size=2;").AsyncSqlCall<List<A>>(c, CommandType.Text, handleResultAsync, dict).Dump());

    Parallel.ForEach(
        impersonatedCalls,
        c => new SqlSync("Data Source=devsql2;Initial Catalog=Test;Integrated Security=true;Max Pool Size=2;").SyncSqlCall<List<A>>(c, CommandType.Text, handleResultSync, dict).Dump());


    // Scenario 2: Normal calls, with connection pooling -- should succeed every time
    Parallel.ForEach(
        calls,
        c => new SqlAsync("Data Source=devsql2;Initial Catalog=Test;Integrated Security=true;Max Pool Size=2;").AsyncSqlCall<List<A>>(c, CommandType.Text, handleResultAsync, dict).Dump());

    Parallel.ForEach(
        calls,
        c => new SqlSync("Data Source=devsql2;Initial Catalog=Test;Integrated Security=true;Max Pool Size=2;").SyncSqlCall<List<A>>(c, CommandType.Text, handleResultSync, dict).Dump());



// Scenario 3: Impersonated Calls, WITHOUT connection pooling -- should succeed every time
    Parallel.ForEach(
        impersonatedCalls,
        c => new SqlAsync("Data Source=devsql2;Initial Catalog=Test;Integrated Security=true;Max Pool Size=200;").AsyncSqlCall<List<A>>(c, CommandType.Text, handleResultAsync, dict).Dump());

    Parallel.ForEach(
        impersonatedCalls,
        c => new SqlSync("Data Source=devsql2;Initial Catalog=Test;Integrated Security=true;Max Pool Size=200;").SyncSqlCall<List<A>>(c, CommandType.Text, handleResultSync, dict).Dump());
}

public class SqlSync
{
    private readonly string _connectionString;
    public int Timeout {get; set;}

    public SqlSync(string connString)
    {
        _connectionString = connString;
        Timeout = 30;
    }

    public T SyncSqlCall<T>(string commandText, CommandType type, Func<SqlDataReader, T> handleResult, Dictionary<string, object> parameters = null)
    {
        using (SqlConnection conn = new SqlConnection(_connectionString))
        using (SqlCommand cmd = new SqlCommand(commandText, conn))
        {
            cmd.CommandTimeout = Timeout;
            cmd.CommandType = CommandType.Text;

            if (parameters != null)
            {
                foreach (KeyValuePair<string, object> kvp in parameters)
                    cmd.Parameters.AddWithValue(kvp.Key, kvp.Value ?? DBNull.Value);
            }

            conn.Open();

            using (var rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                return handleResult(rdr);
        }
    }
}

public class SqlAsync
{
    private readonly string _connectionString;
    public int Timeout { get; set; }

    public SqlAsync(string connString)
    {
        _connectionString = connString;
        Timeout = 30;
    }

    public Task<T> AsyncSqlCall<T>(string sp, CommandType commandType, Func<SqlDataReader, Task<T>> handleResult, Dictionary<string, object> parameters = null)
    {
        return AsyncSqlCall<T>(sp, commandType, (reader, token) => handleResult(reader), CancellationToken.None, parameters);
    }

    public async Task<T> AsyncSqlCall<T>(string commandText, CommandType type, Func<SqlDataReader, CancellationToken, Task<T>> handleResult, CancellationToken cancellationToken, Dictionary<string, object> parameters = null)
    {
        using (SqlConnection conn = new SqlConnection(_connectionString))
        using (SqlCommand cmd = new SqlCommand(commandText, conn))
        {
            cmd.CommandTimeout = Timeout;
            cmd.CommandType = CommandType.Text;

            if (parameters != null)
            {
                foreach (KeyValuePair<string, object> kvp in parameters)
                    cmd.Parameters.AddWithValue(kvp.Key, kvp.Value ?? DBNull.Value);
            }

            await conn.OpenAsync(cancellationToken);

//          if (conn.State != ConnectionState.Open)
//              await Task.Delay(TimeSpan.FromMilliseconds(10));
            using (var rdr = await cmd.ExecuteReaderAsync(CommandBehavior.CloseConnection, cancellationToken))
                return await handleResult(rdr, cancellationToken);
        }
    }
}

public class A
{
    public string object_name { get; set; }
}

public static Func<SqlDataReader, Task<List<A>>> handleResultAsync = (SqlDataReader sdr) =>
{
    var result = new List<A>();
    while (sdr.Read())
    {
        result.Add(new A { object_name = sdr.GetFieldValue<string>(0) });
    }
    return Task.FromResult(result);
};

public static Func<SqlDataReader, List<A>> handleResultSync = (SqlDataReader sdr) =>
{
    var result = new List<A>();
    while (sdr.Read())
    {
        result.Add(new A { object_name = sdr.GetFieldValue<string>(0) });
    }
    return result;
};

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.