5

Has anyone else has seen this, Or knows anything else i should look for. I'm getting an error on our sql server, the error will happen, then about 50% of the time the SQL cluster service will fail. Causing the AG to flip to the other server. I've created a case as a suspect it's a bug, but i haven't heard back yet.

I have restarted, and run a checkdb on all the databases.

Error Messages:

Date 1/24/2022 4:21:53 PM Log SQL Server (Current - 1/24/2022 5:10:00 PM)

Source spid1063

Message SQL Server Assertion: File: <"interop.cpp">, line=125 Failed Assertion = 'nullptr != error'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted. Date 1/24/2022 4:21:53 PM Log SQL Server (Current - 1/24/2022 5:10:00 PM)

Source spid1063

Message A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a Hotfix from Technical Support.

Edit: adding some details. 15.0.4188.2, SQL AG on 3 server, in Asynchronies, Manual failover. Memory optimized Temp DB is on. These two windows events match with the timing of the sql events.

Windows Server Failover Cluster did not receive a process event signal from SQL Server hosting availability group 'AG' within the lease timeout period.

The lease between availability group 'AG' and the Windows Server Failover Cluster has expired. A connectivity issue occurred between the instance of SQL Server and the Windows Server Failover Cluster. To determine whether the availability group is failing over correctly, check the corresponding availability group resource in the Windows Server Failover Cluster.

I relaxed heartbeat and thresholds to see if that would prevent the AG from flipping while it's investigated.
https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/hadr-cluster-best-practices?tabs=windows2012#heartbeat-and-threshold

These settings seem to prevent the flipping, but I'm still getting the nullptr issue. which is still being investigated.

Improve this question
0

1 Answer 1

Reset to default
6

It's an issue and should be fixed in an upcoming patch. In the interim you can disable in memory optimized TempDB and that should solve the issue (until the patch, then you can turn it back on).

The reason the AG fails is due to the checks failing as the application threads are frozen for the duration of the dump generation, thus they won't respond and either the lease or the *alive checks will fail.

Improve this answer
6
  • 1
    We are currently hitting this on CU15 (15.0.4198.2), and I don't see this issue addressed in CU16 either. Do you know if it was quietly added to CU16 or if it hasn't quite made the CU train yet?
    – Aaron Bertrand
    Jul 20, 2022 at 16:14
  • 1
    @AaronBertrand I checked, the fix has not yet been published to any CUs. It's currently set for the next CU for 2019, but that doesn't mean it'll make it.
    – Sean Gallardy
    Jul 21, 2022 at 15:03
  • 1
    Thanks, I don't currently have a repro that is either reliable or easy. We have a (quite convoluted and long-in-the-tooth) nightly process that uses a ton of tempdb resources (e.g. select 80 million rows into two #heaps, then use those to create more #temp tables via joins) and this assertion stack dump happens about once every 10 days.
    – Aaron Bertrand
    Jul 21, 2022 at 15:27
  • There are definitely some tuning opportunities in that process but, in the meantime, our only real option is to disable the feature (and check all other servers to make sure we haven't been missing these assertions happening there, too).
    – Aaron Bertrand
    Jul 21, 2022 at 15:29
  • After another crash dump today, and without any hint of a fix, we have now disabled the mem-opt tempdb feature on five different instances of SQL Server 2019 (now on CU16). We have also reversed our existing policy of "turn it on until something bad happens" to "just turn it off everywhere until we can prove something good might happen." :-( And I'm not sure when we'll investigate it again.
    – Aaron Bertrand
    Nov 8, 2022 at 16:54

Your Answer

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

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