SQL Server 2019 process crashes on specific query

Question

Hello,

I have a strange issue where I can crash SQL Server 2019 (fully patched at the time of writing to 15.0.4455.2) by issuing rather simple query with the following prerequisites:

• there is a multi statement table value function
• there is a view that does simple select from the mentioned function
• issuing simple query between begin tran and rollback tran with where clause on the mentioned view will crash the SQL Server process on the rollback tran line

The problem is easily reproducible (tried on a two fully patched SQL Server 2019 machines - one freshly installed) by issuing the following code from SSMS:

begin tran

go
create function fnDummy()
returns @tbl table (ID int)
begin
  return
end
go
create view vDummy
as
  select * from fnDummy()
go

select * from vDummy where ID=1

rollback tran

When it actually works, simply restarting SQL Server service and running the above query causes it to reappear again.
At the time of the crash I'm getting two consecutive Event ID 1203 events from MSSQLSERVER with something along the lines of:

Process ID nnn attempted to unlock a resource it does not own: DATABASE: mmm:0 [PLANGUIDE]...  

Any help/guidance is highly appreciated. Thanks!

Answer 1

This is obviously a bug in SQL Server and an ugly one.

I tested your repro, and found that:

• On SQL 2019, the entire SQL Server process dies.
• On SQL 2022, only my own process dies, but SQL Server survives.
• On SQL 2025, the batch completes successfully.

But I am not sure that this means that the issue has been fixed. Do I understand you correctly that sometimes you can run this without errors, but if you restart SQL Server the error starts to appear?

The full error message reads:

Process ID 75 attempted to unlock a resource it does not own: DATABASE: 2:0 [PLANGUIDE]. Retry the transaction, because this error may be caused by a timing condition. If the problem persists, contact the database administrator.

So it could be that is pure luck that it completes without errors on my SQL 2025 instance.

I also find that if I add a go before the rollback statement, so that the ROLLBACK statement come in a separate batch, I don't seem to get the error.

The way to deal with bugs like this is to open a support case with Microsoft. However, since SQL 2019 is out of mainstream support, it is unlikely that Microsoft will fix this in SQL 2019.

I would assume that if this something you run into, you can easily work around the issue, so it is not really blocking you, am I right?

Answer 2

The issue you are experiencing with SQL Server 2019 crashing upon executing a specific query seems to be related to the interaction between the multi-statement table-valued function and the view you created. The error message you mentioned, particularly the Event ID 1203, indicates that there is a resource unlocking issue, which could be a bug or a known issue in SQL Server.

Here are some steps you can take to troubleshoot and potentially resolve the problem:

1. Check for Updates: Ensure that your SQL Server is fully updated. You mentioned that it is patched to version 15.0.4455.2; however, check if there are any newer cumulative updates or hotfixes available that may address this issue.
2. Recreate the Function and View: Sometimes, recreating the function and view can resolve underlying issues. Drop and recreate both the function and the view to see if the problem persists.
3. Simplify the Query: If possible, try to simplify the query further or break it down into smaller parts to identify if a specific part of the query is causing the crash.
4. Review SQL Server Logs: Look into the SQL Server error logs for any additional details surrounding the crash. This might provide more context on what is happening at the time of the crash.
5. Contact Microsoft Support: Since this is a reproducible crash, consider reaching out to Microsoft Support for assistance. They may have additional insights or workarounds for this specific issue.
6. Consider Alternative Approaches: If this issue is blocking your work, consider using alternative methods to achieve the same result without using the multi-statement table-valued function, if feasible.

This type of issue can be complex, and if it is a bug, it may require a fix from Microsoft.

---

References:

• KB5017593 - Cumulative Update 18 for SQL Server 2019
• KB5027702 - Cumulative Update 22 for SQL Server 2019