Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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.
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.
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.
OPTION (QUERYTRACEON 9481) to use the original CE --- but none of this is the point. Your database is corrupted.