If you are running SQL Server 2017 or SQL Server 2019 on Redhat 7.x or UBUNTU 16.x Linux operating systems and configured Transactional Replication or Change Data Capture feature, then sporadically log reader agent may fail to read the transactional logs with non yield dump or SQL Server core dump may be created
You might see following spinlock message in Linux /var/log/messages file or SQL Server error log.
0311305655_1586954331_applogs\var\log\messages 14712 Apr 15 07:39:08 ip-10-57-146-76 sqlservr: getspinlock pre-Sleep(): spid 0, 271030 yields on lock type "XDESMGR" (adr 0000001B7F3496C0)
0311305655_1586954331_applogs\var\log\messages 14713 Apr 15 07:39:09 ip-10-57-146-76 sqlservr: getspinlock pre-Sleep(): spid 0, 272641 yields on lock type "XDESMGR" (adr 0000001B7F3496C0)
0311305655_1586954331_applogs\var\log\messages 14721 Apr 15 07:39:18 ip-10-57-146-76 sqlservr: 2020-04-15 07:39:18.18 Server * Non-yielding Scheduler
2020-02-11 12:05:31.35 Server Process 0:0:0 (0x2a4) Worker 0x00000015AADA8160 appears to be non-yielding on Scheduler 3. Thread creation time: 13225913261587. Approx Thread CPU Used: kernel 0 ms, user 54770 ms. Process Utilization 45%. System Idle 0%. Interval: 70012 ms.
2020-02-11 12:05:41.36 Server Process 0:0:0 (0x268) Worker 0x00000015AFF12160 appears to be non-yielding on Scheduler 1. Thread creation time: 13225913261537. Approx Thread CPU Used: kernel 0 ms, user 61160 ms. Process Utilization 48%. System Idle 0%. Interval: 76426 ms.
This issue is currently investigated by SQL Server engineering team and fix will be released in upcoming CU’s.
Mean time you can enable Forced Unit Access (FUA) which will ensure the data is stored in stable media before a write request return to SQL Server using following parameters will workaround the issue.
sudo /opt/mssql/bin/mssql-conf traceflag 3979 on
sudo /opt/mssql/bin/mssql-conf set control.alternatewritethrough 0
sudo /opt/mssql/bin/mssql-conf set control.writethrough 1
After enabling the trace flag we may need to restart SQL Server for TF to take effect.
More information about FUA can be found in following blog and KB article
https://bobsql.com/sql-server-on-linux-forced-unit-access-fua-internals/
https://support.microsoft.com/en-us/help/4131496/enable-forced-flush-mechanism-in-sql-server-2017-on...
