2

SQL Server 2014 SP3+CU4.

Database compatibility level is 120.

My SQL is like:

select top 1 id
from xxx
where col1=xxx

Executing this, SQL Server reports:

message 596, severity 21, state 1, line 6
Cannot continue the execution because the session is in the kill state.

If executed without where or let where id=xxx, no error, but where col1|col2|col3 = xxx error again.

If I change compatibility level to 110 or 100, no error.

1
  • This database is restored from production-db's backup, the prod-db running on sqlserver2008R2, it worked fine many years, considering that EF Core does not support 2008R2, so I prepare upgrade it to 2014, so I installed 2014 on my computer and restored it, for test, and meet this issue. so I don't think the problem is from my backup, actually it worked with compatibility level 100/110 on 2014.
    – ahdung
    Commented Jun 4, 2021 at 3:09

1 Answer 1

3

The error message indicates that an unhandled exception occurred within SQL Server itself.

This may occur as the result of a product defect, or a corrupted database.

You should run a DBCC CHECKDB and take appropriate corrective action. That might mean restoring from the latest backup you have without the corruption present. You can narrow that down by referring to the last time your routine DBCC CHECKDB runs completed without errors.

You might also want to try dropping any existing statistics objects on the table concerned, and let the engine recreate them as needed. Statistics corruption is one possible cause (of many) that might not be picked up by DBCC CHECKDB.

If there is no corruption, contact Microsoft Support for assistance, since you are already on the latest cumulative update for SQL Server 2014. Note that this version is out of mainstream support, so your options may be limited, or expensive.


You cannot fix corruption in system tables or views by updating them directly. That sort of corruption absolutely can cause the problems you are seeing. You need to restore the database from a backup.

The compatibility level is coincidence in that your current query happens not to need the corrupt data when compiling an execution plan. That won't remain the case forever. You need to fix the root cause now.

If the last good backup is too old, you can try to copy data from the current database to a new one. That becomes an exercise in disaster mitigation rather than recovery.

5
  • Thank you. dbcc checkdb got few issues, all about sys.sql_expression_dependencies, catelog message 3854. I tried update this view under server single mode+DAC, but not success, tell me this view can't be update. I think this isn't reason, because change compatibility level work.
    – ahdung
    Commented Jun 3, 2021 at 8:00
  • backup is newly, from a worked many years production db. A simple query that caused a severity level 21 error is beyond my expectation, at least sqlserver should tell me where the problem is, it is really frustrating.
    – ahdung
    Commented Jun 4, 2021 at 3:19
  • @ahdung It is telling you where the problem is. One or more system tables is corrupted. The way to recover from corruption is to restore from your backup and transaction log chain.
    – Paul White
    Commented Jun 4, 2021 at 3:35
  • How can you sure that is the reason? why query success under other compatibility level?
    – ahdung
    Commented Jun 5, 2021 at 0:25
  • @ahdung Why are you resisting the idea that a corruption in system structures can lead to unhandled exceptions? Compatibility level 120 activates a different cardinality estimator (part of query optimization), which means different code is executed inside the engine. You might find your test queries complete successfully with OPTION (QUERYTRACEON 9481) to use the original CE --- but none of this is the point. Your database is corrupted.
    – Paul White
    Commented Jun 7, 2021 at 6:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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