Log reader agent may raise a Non-yielding Scheduler dump in SQL Server running on Linux OS

This post has been republished via RSS; it originally appeared at: SQL Server Support articles.

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-linux

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.