August 29, 2023 at 12:09 pm
I have problem with SQL Server 2019. Of course I have new update 2019.150.4322.2 from August 2023 but problem stil exist. SQL Server 2019 Standard. OS Windows 11 PRO. Machine Dell 7910 2xXeon(R) CPU E5-2695 v4 @ 2.10GHz. Dozen cores. Database size about 1GB (about 20 users). Every month between 26th and 28th without any reason SQL Server crashed STOP and START few times with dumping some files and finall error message "Non-yielding Scheduler". Stange that this error happen only one per month. Users told me that they can not connect and after some time database work fine for next few minutes. Best solution is restart PC ... but next month the same problem happen. My hard discs are fine (SSD NVME), Windows 11 PRO and SQL Standard 2019 have latest updates. I don't have idea what to check and what to do next.
Windows error message:
Error package 1308003314336149797, type 5
Event name: SQLException64
Answer: Not available
CAB File ID: 0
Problem signature:
P1: sqlservr.exe
P2: 0.0.0.0
P3: 0000000000000000
Q4: sqlllang.dll
Q5: 2019.150.4316.3
P6: 000000006479414E
Q7: 0
P8: 00000000010074A0
P9: 0000000000000158
Q10:
Included files:
\\?\C:\Program Files\Microsoft SQL Server\MSSQL15.SQLSERVER2019\MSSQL\Log\SQLDump0010.mdmp
\\?\C:\Program Files\Microsoft SQL Server\MSSQL15.SQLSERVER2019\MSSQL\Log\SQLDump0010.txt
\\?\C:\Program Files\Microsoft SQL Server\MSSQL15.SQLSERVER2019\MSSQL\Log\SQLDump0010.log
\\?\C:\ProgramData\Microsoft\Windows\WER\Temp\WER.0ccb4e91-3f90-4d53-89aa-ec958eb20895.tmp.WERInternalMetadata.xml
\\?\C:\ProgramData\Microsoft\Windows\WER\Temp\WER.214246a4-7052-4134-b7e7-a9c806fb343d.tmp.xml
\\?\C:\ProgramData\Microsoft\Windows\WER\Temp\WER.cc578547-4603-46a2-85e0-ecf7e15478fe.tmp.csv
\\?\C:\ProgramData\Microsoft\Windows\WER\Temp\WER.4bd93cb3-ac26-4c92-a741-4b480a10a17e.tmp.txt
These files may be available here:
\\?\C:\ProgramData\Microsoft\Windows\WER\ReportArchive\Critical_sqlservr.exe_b6043259295d3ca1d65b04f6062de26b5d7c0a4_00000000_ebbab210-b557-4fbd-922e-2f3ba0dbd546
Analysis symbol:
Re-searching for a solution: 0
Report ID: ebbab210-b557-4fbd-922e-2f3ba0dbd546
Report status: 2147487744
Bundle hash value: 38d0a63c97e51df89226f615940a3125
CAB file GUID: 0
I attached decodecd last 2 mdmp dum files by WIndows Debugging Tool.
DUMP 1
Microsoft (R) Windows Debugger Version 10.0.22621.755 AMD64
Copyright (c) Microsoft Corporation. All rights reserved.
Loading Dump File [D:\zz\SQLDump0011.mdmp]
Comment: 'Stack Trace'
Comment: 'Stalled IOCP Listener'
Comment: '<Identity><Element key="BranchName" val="sql2019_rtm_qfe-cu21"/><Element key="OfficialBuild" val="true"/><Element key="BuildFlavor" val="Release (GoldenBits)"/><Element key="QBuildGuid" val="7cb56c91-0357-a391-53fb-a281857d3561"/><Element key="QBuildSyncChangeset" val="0e46d94e64c94bd7946b9b35ab5e158454cfcbb4"/></Identity>'
User Mini Dump File: Only registers, stack and portions of memory are available
Symbol search path is: srv*
Executable search path is:
Windows 10 Version 22000 MP (36 procs) Free x64
Product: WinNt, suite: SingleUserTS
Edition build lab: 22000.1.amd64fre.co_release.210604-1628
Machine Name:
Debug session time: Mon Aug 28 10:33:06.000 2023 (UTC + 2:00)
System Uptime: 7 days 16:09:02.073
Process Uptime: 7 days 16:09:11.000
................................................................
................................................................
......................
Loading unloaded module list
.......................
This dump file has an exception of interest stored in it.
The stored exception information can be accessed via .ecxr.
(1640.24a4): Unknown exception - code 00002000 (first/second chance not available)
For analysis of this file, run !analyze -v
ntdll!NtWaitForSingleObject+0x14:
00007ffd`abc23cb4 c3 ret
0:060> !analyze -v
*******************************************************************************
* *
* Exception Analysis *
* *
*******************************************************************************
*** WARNING: Unable to verify timestamp for sqlservr.exe
*** WARNING: Unable to verify timestamp for sqldk.dll
*** WARNING: Unable to verify timestamp for sqlmin.dll
*** WARNING: Unable to verify timestamp for hkengine.dll
*** WARNING: Unable to verify timestamp for sqllang.dll
*** WARNING: Unable to verify timestamp for XPStar.DLL
KEY_VALUES_STRING: 1
Key : Analysis.CPU.mSec
Value: 440515
Key : Analysis.DebugAnalysisManager
Value: Create
Key : Analysis.Elapsed.mSec
Value: 508204
Key : Analysis.Init.CPU.mSec
Value: 4171
Key : Analysis.Init.Elapsed.mSec
Value: 37083
Key : Analysis.Memory.CommitPeak.Mb
Value: 439
Key : CLR.BuiltBy
Value: NET481REL1LAST_B
Key : CLR.Engine
Value: CLR
Key : CLR.Version
Value: 4.8.9167.0
Key : Timeline.OS.Boot.DeltaSec
Value: 662942
Key : Timeline.Process.Start.DeltaSec
Value: 662951
Key : WER.OS.Branch
Value: co_release
Key : WER.OS.Timestamp
Value: 2021-06-04T16:28:00Z
Key : WER.OS.Version
Value: 10.0.22000.1
Key : WER.Process.Version
Value: 12.0.6828.0
FILE_IN_CAB: SQLDump0011.mdmp
COMMENT: Stack Trace
NTGLOBALFLAG: 0
PROCESS_BAM_CURRENT_THROTTLED: 0
PROCESS_BAM_PREVIOUS_THROTTLED: 0
CONTEXT: (.ecxr)
rax=0000000000000000 rbx=0000000000000000 rcx=0000000000000000
rdx=0000000000000000 rsi=0000000000000000 rdi=0000000000000000
rip=0000000000000000 rsp=0000000000000000 rbp=0000000000000000
r8=0000000000000000 r9=0000000000000000 r10=0000000000000000
r11=0000000000000000 r12=0000000000000000 r13=0000000000000000
r14=0000000000000000 r15=0000000000000000
iopl=0 nv up di pl nz na pe nc
cs=0000 ss=0000 ds=0000 es=0000 fs=0000 gs=0000 efl=00000000
00000000`00000000 ?? ???
Resetting default scope
EXCEPTION_RECORD: (.exr -1)
ExceptionAddress: 00007ffd84fa74a3 (sqllang!stackTrace+0x0000000000000103)
ExceptionCode: 00002000
ExceptionFlags: 00000000
NumberParameters: 1
Parameter[0]: 00000056715dce80
PROCESS_NAME: sqlservr.exe
ERROR_CODE: (NTSTATUS) 0x2000 - <Unable to get error code text>
EXCEPTION_CODE_STR: 2000
EXCEPTION_PARAMETER1: 00000056715dce80
STACK_TEXT:
0000000000000000 0000000000000000 : 0000000000000000 0000000000000000 0000000000000000 0000000000000000 : 0x0
STACK_COMMAND: ~0s; .ecxr ; kb
SYMBOL_NAME: sqllang!stackTrace+103
MODULE_NAME: sqllang
IMAGE_NAME: sqllang.dll
FAILURE_BUCKET_ID: APPLICATION_FAULT_2000_sqllang.dll!stackTrace
OS_VERSION: 10.0.22000.1
BUILDLAB_STR: co_release
OSPLATFORM_TYPE: x64
OSNAME: Windows 10
IMAGE_VERSION: 2019.150.4316.3
FAILURE_ID_HASH: {8ce6b7a3-c5c8-29f7-d70a-4f41f768a70f}
Followup: MachineOwner
---------
DUMP2
Microsoft (R) Windows Debugger Version 10.0.22621.755 AMD64
Copyright (c) Microsoft Corporation. All rights reserved.
Loading Dump File [D:\zz\SQLDump0012.mdmp]
Comment: 'Stack Trace'
Comment: 'Non-yielding Scheduler'
Comment: '<Identity><Element key="BranchName" val="sql2019_rtm_qfe-cu22"/><Element key="OfficialBuild" val="true"/><Element key="BuildFlavor" val="Release (GoldenBits)"/><Element key="QBuildGuid" val="3b45ad97-2717-c53b-4b90-aecbb3566466"/><Element key="QBuildSyncChangeset" val="8df6747b9e0bb1f4cb7fd03548e4a908db2817da"/></Identity>'
User Mini Dump File: Only registers, stack and portions of memory are available
Symbol search path is: srv*
Executable search path is:
Windows 10 Version 22000 MP (36 procs) Free x64
Product: WinNt, suite: SingleUserTS
Edition build lab: 22000.1.amd64fre.co_release.210604-1628
Machine Name:
Debug session time: Mon Aug 28 17:44:12.000 2023 (UTC + 2:00)
System Uptime: 0 days 0:12:15.873
Process Uptime: 0 days 0:12:07.000
................................................................
................................................................
Loading unloaded module list
.....................
This dump file has an exception of interest stored in it.
The stored exception information can be accessed via .ecxr.
(1660.2494): Unknown exception - code 00000000 (first/second chance not available)
For analysis of this file, run !analyze -v
ntdll!NtWaitForSingleObject+0x14:
00007ffb`edda3cb4 c3 ret
0:029> !analyze -v
*******************************************************************************
* *
* Exception Analysis *
* *
*******************************************************************************
Cannot find frame 0x33, previous scope unchanged
Cannot find frame 0x3e, previous scope unchanged
Cannot find frame 0x51, previous scope unchanged
Cannot find frame 0x53, previous scope unchanged
Cannot find frame 0x53, previous scope unchanged
Cannot find frame 0x33, previous scope unchanged
Cannot find frame 0x3e, previous scope unchanged
Cannot find frame 0x51, previous scope unchanged
Cannot find frame 0x121, previous scope unchanged
Cannot find frame 0x121, previous scope unchanged
Cannot find frame 0x121, previous scope unchanged
KEY_VALUES_STRING: 1
Key : Analysis.CPU.mSec
Value: 376077
Key : Analysis.DebugAnalysisManager
Value: Create
Key : Analysis.Elapsed.mSec
Value: 452665
Key : Analysis.Init.CPU.mSec
Value: 1640
Key : Analysis.Init.Elapsed.mSec
Value: 11432
Key : Analysis.Memory.CommitPeak.Mb
Value: 623
Key : CLR.BuiltBy
Value: NET481REL1LAST_B
Key : CLR.Engine
Value: CLR
Key : CLR.Version
Value: 4.8.9167.0
Key : Timeline.OS.Boot.DeltaSec
Value: 735
Key : Timeline.Process.Start.DeltaSec
Value: 727
Key : WER.OS.Branch
Value: co_release
Key : WER.OS.Timestamp
Value: 2021-06-04T16:28:00Z
Key : WER.OS.Version
Value: 10.0.22000.1
Key : WER.Process.Version
Value: 2019.150.4322.2
FILE_IN_CAB: SQLDump0012.mdmp
COMMENT: Stack Trace
NTGLOBALFLAG: 0
PROCESS_BAM_CURRENT_THROTTLED: 0
PROCESS_BAM_PREVIOUS_THROTTLED: 0
CONTEXT: (.ecxr)
rax=0000000000000000 rbx=0000000000000000 rcx=0000000000000000
rdx=0000000000000000 rsi=0000000000000000 rdi=0000000000000000
rip=0000000000000000 rsp=0000000000000000 rbp=0000000000000000
r8=0000000000000000 r9=0000000000000000 r10=0000000000000000
r11=0000000000000000 r12=0000000000000000 r13=0000000000000000
r14=0000000000000000 r15=0000000000000000
iopl=0 nv up di pl nz na pe nc
cs=0000 ss=0000 ds=0000 es=0000 fs=0000 gs=0000 efl=00000000
00000000`00000000 ?? ???
Resetting default scope
EXCEPTION_RECORD: (.exr -1)
ExceptionAddress: 00007ffbc24bf850 (sqllang!stackTrace+0x0000000000000100)
ExceptionCode: 00000000
ExceptionFlags: 00000000
NumberParameters: 0
PROCESS_NAME: sqlservr.exe
FAULTING_THREAD: 00002494
STACK_TEXT:
0000000000000000 0000000000000000 sqlservr.exe!unknown_error_in_process+0x0
0000000000000000 0000000000000000 unknown![.ecxr]+0x0
00000024cf1db918 00007ffbedda3cb4 ntdll!NtWaitForSingleObject+0x0
00000024cf1db920 00007ffbeb7a0e9e KERNELBASE!WaitForSingleObjectEx+0x0
00000024cf1db9c0 00007ff67992bcde sqlservr!CDmpDump::InvokeSqlDumper+0x0
00000024cf1dbac0 00007ff67992ba53 sqlservr!CDmpDump::DumpInternal+0x0
00000024cf1dbb70 00007ff67992b754 sqlservr!CDmpDump::Dump+0x0
00000024cf1dbbb0 00007ffbc1851a33 sqllang!SQLDumperLibraryInvoke+0x0
00000024cf1dbbf0 00007ffbc24f46c5 sqllang!SQLLangDumperLibraryInvoke+0x0
00000024cf1dbcb0 00007ffbc24f5556 sqllang!CImageHelper::DoMiniDump+0x0
00000024cf1dbed0 00007ffbc24c0192 sqllang!stackTrace+0x0
00000024cf1dd8f0 00007ff679903eb5 sqlservr!SQL_SOSNonYieldSchedulerCallback+0x0
00000024cf1fdb90 00007ffbc0dd7dc0 sqldk!SOS_OS::ExecuteNonYieldSchedulerCallbacks+0x0
00000024cf1fde40 00007ffbc0dae77b sqldk!SOS_Scheduler::ExecuteNonYieldSchedulerCallbacks+0x0
00000024cf1fe020 00007ffbc0d436bd sqldk!SchedulerMonitor::CheckScheduler+0x0
00000024cf1fe1c0 00007ffbc0d42d42 sqldk!SchedulerMonitor::CheckSchedulers+0x0
00000024cf1feb30 00007ffbc0d425e2 sqldk!SchedulerMonitor::Run+0x0
00000024cf1fec30 00007ffbc0e45e19 sqldk!SchedulerMonitor::EntryPoint+0x0
00000024cf1fec60 00007ffbc0d39e63 sqldk!SOS_Task::Param::Execute+0x0
00000024cf1ff260 00007ffbc0d3a4af sqldk!SOS_Scheduler::RunTask+0x0
00000024cf1ff2d0 00007ffbc0d3a06e sqldk!SOS_Scheduler::ProcessTasks+0x0
00000024cf1ff3f0 00007ffbc0d5a8b2 sqldk!SchedulerManager::WorkerEntryPoint+0x0
00000024cf1ff4c0 00007ffbc0d5b6f4 sqldk!SystemThreadDispatcher::ProcessWorker+0x0
00000024cf1ff7c0 00007ffbc0d5b458 sqldk!SchedulerManager::ThreadEntryPoint+0x0
00000024cf1ff8b0 00007ffbed0155a0 kernel32!BaseThreadInitThunk+0x0
00000024cf1ff8e0 00007ffbedd0485b ntdll!RtlUserThreadStart+0x0
SYMBOL_NAME: sqlservr!CDmpDump::InvokeSqlDumper+1ee
MODULE_NAME: sqlservr
IMAGE_NAME: sqlservr.exe
STACK_COMMAND: .ecxr ; kb ; ** Pseudo Context ** Pseudo ** Value: ffffffff ** ; kb
FAILURE_BUCKET_ID: SQLSERVER_NON_YIELDING_SCHEDULER_INVALID_COPIED_STACK_0_sqlservr.exe!CDmpDump::InvokeSqlDumper
EXCEPTION_CODE_STR: 0
OS_VERSION: 10.0.22000.1
BUILDLAB_STR: co_release
OSPLATFORM_TYPE: x64
OSNAME: Windows 10
IMAGE_VERSION: 2019.150.4322.2
FAILURE_ID_HASH: {e8ca9172-a26f-0b44-bad0-8d46f5d9350b}
Followup: MachineOwner
---------
Txt file connect with mdump files
=====================================================================
BugCheck Dump
=====================================================================
This file is generated by Microsoft SQL Server
version 15.0.4316.3
upon detection of fatal unexpected error. Please return this file,
the query or program that produced the bugcheck, the database and
the error log, and any other pertinent information with a Service Request.
Computer type is Intel(R) Xeon(R) CPU E5-2695 v4 @ 2.10GHz.
Bios Version is DELL - 1072009
A34
72 X64 level 8664, 10 Mhz processor (s).
Windows NT 10.0 Build 22000 CSD .
Memory
MemoryLoad = 21%
Total Physical = 130992 MB
Available Physical = 103310 MB
Total Page File = 150448 MB
Available Page File = 120079 MB
Total Virtual = 134217727 MB
Available Virtual = 134051413 MB
**Dump thread - spid = 0, EC = 0x0000000000000000
***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL15.SQLSERVER2019\MSSQL\LOG\SQLDump0010.txt
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 08/28/23 03:04:56 spid 9528
*
* Non-yielding Scheduler
*
* *******************************************************************************
* -------------------------------------------------------------------------------
* Short Stack Dump
=====================================================================
BugCheck Dump
=====================================================================
This file is generated by Microsoft SQL Server
version 15.0.4322.2
upon detection of fatal unexpected error. Please return this file,
the query or program that produced the bugcheck, the database and
the error log, and any other pertinent information with a Service Request.
Computer type is Intel(R) Xeon(R) CPU E5-2695 v4 @ 2.10GHz.
Bios Version is DELL - 1072009
A34
72 X64 level 8664, 10 Mhz processor (s).
Windows NT 10.0 Build 22000 CSD .
Memory
MemoryLoad = 7%
Total Physical = 130992 MB
Available Physical = 120668 MB
Total Page File = 150448 MB
Available Page File = 137396 MB
Total Virtual = 134217727 MB
Available Virtual = 134051527 MB
**Dump thread - spid = 0, EC = 0x0000000000000000
***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL15.SQLSERVER2019\MSSQL\LOG\SQLDump0012.txt
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 08/28/23 17:44:10 spid 9364
*
* Non-yielding Scheduler
*
* *******************************************************************************
* -------------------------------------------------------------------------------
* Short Stack Dump
August 29, 2023 at 1:02 pm
Raise a ticket with Microsoft, they have all the proper tools needed to diagnose the dumps and find out whats causing the issue.
August 29, 2023 at 3:26 pm
Windows 11 PRO??? You need to have a Windows Server. I'm surprised that your SQL Standard Edition even runs on that desktop edition.
TBH, I'm thinking it might be automatic reboots due to Windows Updates on the desktop version.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 29, 2023 at 3:39 pm
IOCP = I/O (disk) Completion Port - After SQL Server makes an asynchronous storage request to the host OS, the OS will return data and notifications through the I/O Completion Port
A Non-Yielding Scheduler is a SQL Server thread (called a Scheduler in SQL Server's User Mode Scheduling architecture) spinning on CPU and refusing to yield. ('Yield': the thread suspends its actions, records a wait state, and allows another thread to use the CPU.)
Normally, when a thread cannot get a resource it needs (such as a lock on a row), the thread exits and waits until it gets the resource. There are cases where this does not happen, and it's a problem, so you get a stack dump. Note that Non-Yielding Scheduler problems are usually caused by factors outside the SQL Server instance.
Looks like a SQL Server thread was waiting for I/O and refused to yield. You will see the same results during a 'stuck' I/O operation - SQL made a disk call that's taking too long to respond, and it's not responding in the normal 'disk is just slow right now' way.
As mentioned above, the cause is often outside of SQL Server. I would check the entire path between SQL Server and all of its storage, whether that's internal storage cards or external storage. This is happening monthly. Was a SAN getting patched? Is there a monthly failover of switches between the server and its storage? If it's in a cluster, check what other things were going on in the cluster at the time. If it's a VM, check the hypervisor and its storage, etc., etc., etc.
If it looks like it's happening on a schedule, then go look for a matching schedule elsewhere in your system.
Eddie Wuerch
MCM: SQL
August 29, 2023 at 5:19 pm
OK guys. Thank you for suggestions.
Why I installed SQL Server Standard on Windows 11 PRO.
We are trade company and using ERP software on this PC.
SQL Database of this ERP software reached 1GB so accordance with the licensing rules for this software we must to change SQL Express version to SQL Standard version. We don't need spend more money for Windows Server CAL's etc. because for us the only thing which we need is supporting database bigger then 1GB. We have only dozen users. Buying SQL Server it was too expensive.
I will check if this problem made some Windows Updates. I will freeze all updates for 5 weeks (more then 30 days).
Only mode thing. We have 2 companies.
Old company have PC with SQL Server Standard 2019 with database which reached 1GB (problem is here).
New company have exactly the same PC and configuration with SQL Express Edition 2019 with small database 0,4GB.
Problem is only with SQL database in old company even all hardware and software configurations of both PC's are the same.
August 29, 2023 at 6:31 pm
I am curious if the task prioritization and quantum in Windows 11 that are different in Windows Server.
Desktop operating systems typically implement shorter quanta to make interactive applications seem more responsive while sacrificing some processor time efficiency. Desktop operating systems also give a priority boost to foreground applications that may interfere with SQL - if you are using the machine interactively that runs SQL - stop.
You could try increasing the quantum to match what it is set on Windows server and modify the foreground process prioritization.
But seriously, get a server. The cost of license for a single SQL server, windows license and hardware, is completely irrelevant compared to the cost of labor for 12 people in a single pay period, let alone over the 4-7 years the SQL server will be in production.
August 31, 2023 at 4:08 pm
Also, I could be mistaken, but I am fairly confident that SQL Express handles up to 10 GB Databases, not 1 GB... and quick google confirmed this: https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2019?view=sql-server-ver16
So, for a cost savings plan, you could drop the SQL Server license and buy a windows server license to host the SQL instance. Then provide copies of the software to the 20 end users to use and connect back to the SQL Server. No User CAL's required that way.
About the yielding issue, was that happening when you were on express as well?
I would check if the antivirus is locking the mdf or ldf file too or if an automated defrag process is scheduled (probably not for an SSD... at least shouldn't be). Not certain that is the issue, but I'd start by looking at the scheduled tasks.
How I'd set it up is to have SQL running on server A and application running on local user machines OR have the app on the Win 11 Pro machine you have sitting there. No User CAL required, just need some hardware for the Windows Server to run on and that could even be virtualized (Azure, AWS, etc), or you could pick up a cheap desktop computer to run the server. With 20 users hitting the system, you don't need anything beefy and with the cost savings of switching to SQL Express, you may even end up with extra money in the end!
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 31, 2023 at 7:30 pm
This might also be worth looking at:
https://blog.sqlauthority.com/2019/09/16/sql-server-enable-lock-pages-in-memory-lpim/
August 31, 2023 at 8:46 pm
Yes sure I made this changes according this manual few days ago.
We need to wait for results till 26-28 of September. Every day right know database works fine.
By the way. Yes I set ESET to not check all SQL folders.
I need to confirm that on this server works only for this database. Not other tasks or users on this PC. Users connect from their programs to this SQL database.
September 5, 2023 at 2:24 am
This was removed by the editor as SPAM
September 26, 2023 at 2:49 pm
Today 26th of September problem happen again with the message
Process 0:0:0 (0x3f64) Worker 0x0000020289FB8160 appears to be non-yielding on Scheduler 21. Thread creation time: 13340206857857. Approx Thread CPU Used: kernel 2765 ms, user 0 ms. Process Utilization 0%%. System Idle 93%%. Interval: 73443 ms.
and next message is
Error package, type 0
Event name: SQLException64
Answer: Not available
CAB file ID: 0
Problem signature:
P1: sqlservr.exe
P2: 0.0.0.0
P3: 0000000000000000
P4: sqllang.dll
P5: 2019.150.4322.2
P6: 0000000064C32653
P7: 0
P8: 000000000100F850
P9: 0000000000000158
P10:
and next messages are
Setting database option AUTO_CLOSE to OFF for database 'RABALL_MAG'.
Setting database option PAGE_VERIFY to CHECKSUM for database 'RABALL_MAG'.
Setting database option AUTO_CLOSE to OFF for database 'RABALL_MAG'.
Setting database option PAGE_VERIFY to CHECKSUM for database 'RABALL_MAG'.
Setting database option RECOVERY to SIMPLE for database 'RABALL_UPDATE'.
and then database generated DUMP file like every month, hang for some users and right know works fine.
This are last events on databes before dump.
2023-09-26 00:00:06.47 spid39s This instance of SQL Server has been using a process ID of 5728 since 23.09.2023 12:09:14 (local) 23.09.2023 10:09:14 (UTC). This is an informational message only; no user action is required.
2023-09-26 01:00:02.75 spid86 Error: 18204, Severity: 16, State: 1.
2023-09-26 01:00:02.75 spid86 BackupDiskFile::OpenMedia: Backup device 'E:\backup\re0100\wapro\Archiwum_REBALL_MAG_20230926_0100.ape' failed to open. Operating system error 2(Nie mo|na odnalez okre[lonego pliku.).
2023-09-26 01:00:38.82 Backup Database backed up. Database: REBALL_MAG, creation date(time): 2023/03/11(17:40:27), pages dumped: 1017746, first LSN: 92717:6696:1, last LSN: 92717:6720:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\backup\re0100\wapro\Archiwum_REBALL_MAG_20230926_0100.ape'}). This is an informational message only. No user action is required.
2023-09-26 01:00:38.84 Backup BACKUP DATABASE successfully processed 1017626 pages in 35.939 seconds (221.213 MB/sec).
2023-09-26 04:11:02.91 spid67 Setting database option AUTO_CLOSE to OFF for database 'REBALL_MAG'.
2023-09-26 04:11:02.91 spid67 Setting database option PAGE_VERIFY to CHECKSUM for database 'REBALL_MAG'.
2023-09-26 05:32:48.62 spid66 Setting database option AUTO_CLOSE to OFF for database 'REBALL_MAG'.
2023-09-26 05:32:48.62 spid66 Setting database option PAGE_VERIFY to CHECKSUM for database 'REBALL_MAG'.
2023-09-26 06:07:14.09 spid94 Setting database option AUTO_CLOSE to OFF for database 'REBALL_MAG'.
2023-09-26 06:07:14.09 spid94 Setting database option PAGE_VERIFY to CHECKSUM for database 'REBALL_MAG'.
2023-09-26 06:10:43.19 spid108 Setting database option AUTO_CLOSE to OFF for database 'REBALL_MAG'.
2023-09-26 06:10:43.20 spid108 Setting database option PAGE_VERIFY to CHECKSUM for database 'REBALL_MAG'.
2023-09-26 06:12:25.11 spid132 Setting database option AUTO_CLOSE to OFF for database 'REBALL_MAG'.
2023-09-26 06:12:25.11 spid132 Setting database option PAGE_VERIFY to CHECKSUM for database 'REBALL_MAG'.
2023-09-26 06:23:13.28 spid120 Setting database option AUTO_CLOSE to OFF for database 'REBALL_MAG'.
2023-09-26 06:23:13.28 spid120 Setting database option PAGE_VERIFY to CHECKSUM for database 'REBALL_MAG'.
2023-09-26 06:30:02.37 spid153 Error: 18204, Severity: 16, State: 1.
2023-09-26 06:30:02.37 spid153 BackupDiskFile::OpenMedia: Backup device 'E:\backup\re0100\wapro\Archiwum_REBALL_MAG_JPK_20230926_0630.ape' failed to open. Operating system error 2(Nie mo|na odnalez okre[lonego pliku.).
2023-09-26 06:30:02.46 Backup Database backed up. Database: REBALL_MAG_JPK, creation date(time): 2023/05/24(18:52:52), pages dumped: 3339, first LSN: 53:3208:1, last LSN: 53:3232:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\backup\re0100\wapro\Archiwum_REBALL_MAG_JPK_20230926_0630.ape'}). This is an informational message only. No user action is required.
2023-09-26 06:30:02.47 Backup BACKUP DATABASE successfully processed 3162 pages in 0.058 seconds (425.848 MB/sec).
2023-09-26 06:41:24.07 spid176 Setting database option AUTO_CLOSE to OFF for database 'REBALL_MAG'.
2023-09-26 06:41:24.07 spid176 Setting database option PAGE_VERIFY to CHECKSUM for database 'REBALL_MAG'.
2023-09-26 07:00:02.40 spid195 Error: 18204, Severity: 16, State: 1.
2023-09-26 07:00:02.40 spid195 BackupDiskFile::OpenMedia: Backup device 'E:\backup\re0100\wapro\Archiwum_REBALL_MAG_20230926_0700.ape' failed to open. Operating system error 2(Nie mo|na odnalez okre[lonego pliku.).
2023-09-26 07:00:38.49 Backup Database backed up. Database: REBALL_MAG, creation date(time): 2023/03/11(17:40:27), pages dumped: 1020434, first LSN: 92737:16:110, last LSN: 92737:80:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\backup\re0100\wapro\Archiwum_REBALL_MAG_20230926_0700.ape'}). This is an informational message only. No user action is required.
2023-09-26 07:00:38.50 Backup BACKUP DATABASE successfully processed 1020372 pages in 35.971 seconds (221.613 MB/sec).
2023-09-26 07:03:58.11 spid205 Setting database option AUTO_CLOSE to OFF for database 'REBALL_MAG'.
2023-09-26 07:03:58.11 spid205 Setting database option PAGE_VERIFY to CHECKSUM for database 'REBALL_MAG'.
2023-09-26 08:09:13.13 spid157 Setting database option AUTO_CLOSE to OFF for database 'REBALL_MAG'.
2023-09-26 08:09:13.13 spid157 Setting database option PAGE_VERIFY to CHECKSUM for database 'REBALL_MAG'.
2023-09-26 08:19:49.99 spid149 Setting database option AUTO_CLOSE to OFF for database 'REBALL_MAG'.
2023-09-26 08:19:49.99 spid149 Setting database option PAGE_VERIFY to CHECKSUM for database 'REBALL_MAG'.
2023-09-26 08:32:28.01 spid218 Setting database option AUTO_CLOSE to OFF for database 'REBALL_MAG'.
2023-09-26 08:32:28.01 spid218 Setting database option PAGE_VERIFY to CHECKSUM for database 'REBALL_MAG'.
2023-09-26 09:04:23.87 spid245 Setting database option AUTO_CLOSE to OFF for database 'REBALL_MAG'.
2023-09-26 09:04:23.87 spid245 Setting database option PAGE_VERIFY to CHECKSUM for database 'REBALL_MAG'.
2023-09-26 10:00:02.78 spid212 Error: 18204, Severity: 16, State: 1.
2023-09-26 10:00:02.78 spid212 BackupDiskFile::OpenMedia: Backup device 'E:\backup\re0100\wapro\Archiwum_REBALL_MAG_20230926_1000.ape' failed to open. Operating system error 2(Nie mo|na odnalez okre[lonego pliku.).
2023-09-26 10:00:38.99 Backup Database backed up. Database: REBALL_MAG, creation date(time): 2023/03/11(17:40:27), pages dumped: 1021594, first LSN: 92746:1656:284, last LSN: 92746:1792:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\backup\re0100\wapro\Archiwum_REBALL_MAG_20230926_1000.ape'}). This is an informational message only. No user action is required.
2023-09-26 10:00:39.00 Backup BACKUP DATABASE successfully processed 1021449 pages in 36.049 seconds (221.367 MB/sec).
2023-09-26 12:11:28.47 spid246 Setting database option AUTO_CLOSE to OFF for database 'REBALL_MAG'.
2023-09-26 12:11:28.47 spid246 Setting database option PAGE_VERIFY to CHECKSUM for database 'REBALL_MAG'.
2023-09-26 13:00:02.80 spid310 Error: 18204, Severity: 16, State: 1.
2023-09-26 13:00:02.80 spid310 BackupDiskFile::OpenMedia: Backup device 'E:\backup\re0100\wapro\Archiwum_REBALL_MAG_20230926_1300.ape' failed to open. Operating system error 2(Nie mo|na odnalez okre[lonego pliku.).
2023-09-26 13:00:38.84 Backup Database backed up. Database: REBALL_MAG, creation date(time): 2023/03/11(17:40:27), pages dumped: 1019282, first LSN: 92750:10176:13, last LSN: 92750:10200:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\backup\re0100\wapro\Archiwum_REBALL_MAG_20230926_1300.ape'}). This is an informational message only. No user action is required.
2023-09-26 13:00:38.85 Backup BACKUP DATABASE successfully processed 1019130 pages in 35.941 seconds (221.528 MB/sec).
2023-09-26 16:00:02.81 spid64 Error: 18204, Severity: 16, State: 1.
2023-09-26 16:00:02.81 spid64 BackupDiskFile::OpenMedia: Backup device 'E:\backup\re0100\wapro\Archiwum_REBALL_MAG_20230926_1600.ape' failed to open. Operating system error 2(Nie mo|na odnalez okre[lonego pliku.).
2023-09-26 16:00:39.27 Backup Database backed up. Database: REBALL_MAG, creation date(time): 2023/03/11(17:40:27), pages dumped: 1020434, first LSN: 92752:2208:103, last LSN: 92752:2272:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\backup\re0100\wapro\Archiwum_REBALL_MAG_20230926_1600.ape'}). This is an informational message only. No user action is required.
2023-09-26 16:00:39.29 Backup BACKUP DATABASE successfully processed 1020276 pages in 36.366 seconds (219.185 MB/sec).
2023-09-26 16:13:06.32 Server CImageHelper::Init () Version-specific dbghelp.dll is not used
2023-09-26 16:13:06.32 Server Using 'dbghelp.dll' version '4.0.5'
2023-09-26 16:13:06.32 Server ***Unable to get thread context for spid 0
2023-09-26 16:13:06.32 Server * *******************************************************************************
2023-09-26 16:13:06.32 Server *
2023-09-26 16:13:06.32 Server * BEGIN STACK DUMP:
2023-09-26 16:13:06.32 Server * 09/26/23 16:13:06 spid 9668
2023-09-26 16:13:06.32 Server *
2023-09-26 16:13:06.32 Server * Non-yielding Scheduler
2023-09-26 16:13:06.32 Server *
2023-09-26 16:13:06.32 Server * *******************************************************************************
2023-09-26 16:13:06.32 Server Stack Signature for the dump is 0x0000000000000158
2023-09-26 16:14:10.60 Server Timeout waiting for external dump process 17012.
2023-09-26 16:14:10.60 Server Process 0:0:0 (0x3f64) Worker 0x0000020289FB8160 appears to be non-yielding on Scheduler 21. Thread creation time: 13340206857857. Approx Thread CPU Used: kernel 2765 ms, user 0 ms. Process Utilization 0%. System Idle 93%. Interval: 73443 ms.
Any ideas? During last months works perfect.
Some problems with sqllang.dll or dbghelp.dll ?
Exactly the same problem somebody have here on SQL Server 2019
but our database have Cumulative Update 22 but this problem according this case should be solved in CU14?
September 27, 2023 at 8:12 am
Yesterday i didn't restart database like I do every month. Today morning there was so many mesages error "Non-yielding Scheduler " and users can not login to database so I restarted it. Right know works fine. I also disabled permanently Extended Serveces for next month according this information https://forum.red-gate.com/discussion/85097/upgrade-to-9-0-8-20849-is-creating-sql-log-error-entries-using-dbghelp-dll-version-4-0-5
September 27, 2023 at 8:20 am
Open a case with MSFT and send them the dumps to analyse whats going on.
Although this is worrying
2023-09-26 16:14:10.60 Server Timeout waiting for external dump process 17012.
Looks like something is getting in the way of SQLDumper.exe, do you have anything like CrowdStrike, Bit9, CarbonBlack, McAfee etc etc loaded on the server also?
September 27, 2023 at 10:19 am
Ok thanks you for suggestions.
No I don't have such this software like CrowdStrike, Bit9, CarbonBlack, McAfee.
September 28, 2023 at 5:17 pm
Probably a dumb question, but is there anything interesting in the windows event logs apart from the "application" log? What I mean is, is there anything interesting under System or security or setup or application and service logs (the sub-items in there)?
Also, is there a reason you are turning off auto-close and changing page verify to checksum all the time like that? That feels like it should be a "set once" type operation, not a "repeated operation" to me...
Plus, anything in the windows task scheduler that MAY be causing issues?
If you can clone this system and isolate it, I would be interested to hear if you can force the problem to reoccur on the test environment by doing things like:
A- run all WINDOWS scheduled tasks that are scheduled to run around when this is failing
B- run all SQL scheduled tasks that are scheduled to run around when this is failing (looks like backups?)
C- run a scan with your antivirus/anti-malware against the cloned system with the same settings as live
Another advantage to cloning and isolating it to a new VM (if possible) is that you will be able to rule out 3rd party applications like SQL Monitor (not sure what other 3rd party tools you have). MAY be able to use the clone to narrow down what 3rd party tool is causing the issue (if it is a 3rd party tool).
I am still leaning towards it being some scheduled process (chkdsk, defrag, antivirus, etc) since this is happening only towards the end of the month. And if it is a scheduled process outside of SQL Server, Microsoft may not be able to help.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply