Cannot continue the execution because the session is in the kill state. while building clustered index

Question

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?

Thanks

Answer 1

Common reasons for the state are:

• The system cannot open the device where the data or log file is located
• The specified file was not found during the creation or opening of the physical device
• SQL Server crashes in the middle of the transaction
• Unable to access data or log files when going online due to anti-virus software installed
• Database server is illegally shut down
• Insufficient disk space cannot be written
• SQL cannot complete rollback or roll forward operation
• Database files are locked by third-party backup software

How to solve

Find the cause of the problem first. Please go back to the previous step and check if it is caused by the above reasons. For example, there is not enough disk space.

For the root cause, we must first solve the fundamental problem and then repair the database.

include:

• Check if the system is updated, whether there are serious security risks, whether there is hacking
• Check if the system power is stable
• Check if the disk space is sufficient
• Check if the database file has read and write permissions to the database process
• Check if third-party anti-virus software is installed
• Check if third-party backup software is installed
• Try to mount the database file to another computer

If you have solved the underlying problem, you can follow the steps below. Note that before starting, if the database is a virtual machine, a virtual machine snapshot is highly recommended.

10 step repair method

1. Open SSMS

Open SSMS and connect to the failed SQL Server instance.

2. Preparing to execute SQL

Find the database for the error state, and be careful not to expand it. Just create a new query.

3. Set the database to a state of emergency

Execute the following SQL

EXEC sp_resetstatus database_name;
ALTER DATABASE database_name SET EMERGENCY

This sets the database to an emergency to allow for further fixes.

4. Running an error scan on the database

Execute the following SQL

DBCC CHECKDB (database_name)
DBCC CHECKCATALOG (database_name)

This will run a fault scan on the current fault database. You may see a lot of failures. These errors can be checked in turn. Note that this step will not fix any errors.

5. Preparing to fix

To run a database repair script, you must set the database to be a single user. This can also prevent other people who might be in the process of repairing from accidentally coming in.

At the same time, we want to roll back the most recent transaction, because the last transaction must have failed, and it is likely that the last transaction caused the database failure.

ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

6. Run the fix

DBCC CHECKDB (database_name, REPAIR_FAST) -- quick fix
DBCC CHECKDB (database_name, REPAIR_REBUILD) -- reset index
DBCC CHECKDB (database_name, REPAIR_ALLOW_DATA_LOSS) -- Allows correction of missing data
DBCC CHECKALLOC (database_name, REPAIR_REBUILD) -- Fixed allocation problem
DBCC DBREINDEX (database_name, REPAIR_REBUILD) -- fix index problem

The above code will try to fix the database error. If you are experiencing obstacles during execution, please check the chapter on high frequency issues at the end of the article.

Note that in the above code, REPAIR_ALLOW_DATA_LOSS indicates that data is allowed to be discarded in order to repair the database. The sixth step may take a long time, and it usually takes several hours for a database of around 30GB. Need to wait patiently.

Running the above SQL may not be completely fixed. You can use the following SQL to fix the error multiple times.

DECLARE @Number INT = 1 ;
While @Number < = 10
Begin
PRINT @Number;
SET @Number = @Number + 1 ;
DBCC CHECKDB(database_name, REPAIR_ALLOW_DATA_LOSS)
End

7. Re-allow multi-user connections to the database

At this point, the database has been restored. We can re-allow multiple connections to the database.

ALTER DATABASE database_name SET MULTI_USER

8. Back up now

At this point, the database has reached a usable state. The database should be backed up immediately to export a copy of the available data state. If the database is a virtual machine, it is recommended to take a snapshot at the same time as the backup.

At the same time, it is recommended to restart the entire database server and check if the database is still normal after the restart. This is to avoid the possible cause of repeated database leaks.

9. Checking the data

After the available data is available, we can check if the current database state is a newer state.

10. Fix other errors

At the end of the above process, although most of the data is already available, there are still more errors in the database.

You can view these errors using the following SQL:

DBCC CHECKDB (database_name)

If you want to fix this part of the error, you can try to back up and then restore the backup, which may solve this part of the problem.

FAQ

When the database is in a single-user state, we are unable to leave the current connection. Because once we leave, there may be other connections directly occupied.

In this case, we must manually kill the other connections that were rushed in, ensuring that we are the only user who is operating the database.

Methods as below:

Execute the following SQL first

Select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
  From sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
 Where d.name = 'testdb01'
Go

You will see a list of the output's sessions and find out the SPID of the incoming connection. For example, the SPID is 51.

Execute the following SQL

Kill 51
Go

At this point, we can continue to execute SQL on this single-user database.

Answer 2

Accepted answer by Anduin is the correct answer, this is for the benefit of people searching. This can happen if

1. You are missing a temporary table, and
2. You have made a RAISERROR (sic) call yourself indicating the table is missing

"Cannot continue the execution because the session is in the kill state" is misleading (since it implies your connection session is invalidated; you are still connected) and will send you to posts like this one. However, earlier on in the output you will see the exception message you are actually throwing. Just ignore the distracting scary message that draws your attention away from the actual problem.

Example :

SQL code :

IF OBJECT_ID('tempdb..##example') IS NULL
BEGIN -- include ##example
  RAISERROR('ERROR: table ##example not loaded', 20, -1) WITH LOG
  -- This is often done to give intellisense hints when including tables
  CREATE TABLE ##example(ID INT, mydata VALUE)
END -- include ##example

Error message :

Msg 2745, Level 16, State 2, Line 3
Process ID 57 has raised user error 50000, severity 20. SQL Server is terminating this process.
Msg 50000, Level 20, State 1, Line 3
ERROR: table ##example not loaded
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.
Completion time: 2019-11-27T08:28:21.1089985-07:00`

Answer 3

Similar to sajjad's issue, I was getting this error while using Temporal Tables in SQL Server 2017. I reproduced it from scratch. Below is the smallest example I managed. Upgrading to SQL Server 2019 solved my issue.

USE master
DROP DATABASE IF EXISTS MyTest
GO 
CREATE DATABASE MyTest
GO 
USE MyTest

CREATE TABLE MyParent (
    ID int NOT NULL,
    CONSTRAINT [PK_TestGroupDetails] PRIMARY KEY (ID)
);

CREATE TABLE MyChild (
    ID int NOT NULL,
    MyParentID int NOT NULL,
    Dummy BIT NOT NULL,
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START, 
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime),
    CONSTRAINT [PK_Child] PRIMARY KEY (ID),
    CONSTRAINT [FK_Parent] FOREIGN KEY (MyParentID) REFERENCES MyParent (ID) ON DELETE CASCADE
);

-- To avoid the "Cannot continue the execution because the session is in the kill state", I can do any of the following:
-- Remove this DROP COLUMN line only
-- Remove all references to the Dummy column
-- Remove the Dummy column from the initial CREATE TABLE, and uncomment the ADD line below 
-- Move this DROP COLUMN line so that it occurs after the SET (SYSTEM_VERSIONING = ON)
-- Remove the ON DELETE CASCADE
--ALTER TABLE MyChild ADD Dummy BIT 
ALTER TABLE MyChild DROP COLUMN Dummy

ALTER TABLE MyChild SET (SYSTEM_VERSIONING = ON)

DELETE FROM MyParent

Answer 4

I have received this error when using an index hint in a query, if the index is corrupted

[...]
INNER JOIN  WeigherReport AS Report WITH INDEX([IX_weigherYY])) ON ProdRun.Id = Report.ProductionRun

The solution was to drop and recreate the offending index (in this case IX_weigherYY).