Windows 11 + Samsung 980 踩坑：在 LocalDB 15.0 实例启动期间出错: 无法启动 SQL Server 进程(附赠 查询指定日期范围内的前1000条SQL执行记录)

Windows 11 + Samsung 980 踩坑：在 LocalDB 实例启动期间出错: 无法启动 SQL Server 进程

起因

用 Microsoft Visual Studio 2022 连接 (localdb)\MSSQLLocalDB，等待半天没连接成功。

几经搜索，一顿操作猛如虎，反正数据不重要，先删后重建：

PS C:\Users\Administrator> sqllocaldb delete MSSQLLocalDB
LocalDB instance "MSSQLLocalDB" deleted.
PS C:\Users\Administrator> sqllocaldb create MSSQLLocalDB -s
Creation of LocalDB instance "MSSQLLocalDB" failed because of the following error:
在 LocalDB 实例启动期间出错: 无法启动 SQL Server 进程。

看到这个新的错误，我懵了。
按 Win + R 键，打开运行窗口，输入 eventvwr，打开 事件查看器。
翻 Windows 事件日志，在 Windows 日志 ——> 应用程序 下，发现3条蛛丝马迹：

事件ID：1000
来源：Application Error
事件数据：

错误应用程序名称: sqlservr.exe，版本: 2019.150.4153.1，时间戳: 0x60f610ce
错误模块名称: ntdll.dll，版本: 10.0.22000.778，时间戳: 0x5398ab6f
异常代码: 0xc0000005
错误偏移量: 0x0000000000035c7e
错误进程 ID: 0x1784
错误应用程序启动时间: 0x01d89aaf6ee85153
错误应用程序路径: C:\Program Files\Microsoft SQL Server\150\LocalDB\Binn\sqlservr.exe
错误模块路径: C:\Windows\SYSTEM32\ntdll.dll
报告 ID: c1912614-645d-48cf-903b-aa73054de47b
错误程序包全名: 
错误程序包相对应用程序 ID: 

事件ID：1001
来源：Windows Error Reporting
事件数据：

故障存储段 ，类型 0
事件名称: APPCRASH
响应: 不可用
Cab ID: 0

问题签名:
P1: sqlservr.exe
P2: 2019.150.4153.1
P3: 60f610ce
P4: ntdll.dll
P5: 10.0.22000.778
P6: 5398ab6f
P7: c0000005
P8: 0000000000035c7e
P9: 
P10: 

附加文件:
\\?\C:\ProgramData\Microsoft\Windows\WER\Temp\WER.cad8ce7d-17e3-469d-be84-c89fcfc3de86.tmp.mdmp
\\?\C:\ProgramData\Microsoft\Windows\WER\Temp\WER.ac119f5e-7188-4361-ba0d-aaed83150fb8.tmp.WERInternalMetadata.xml
\\?\C:\ProgramData\Microsoft\Windows\WER\Temp\WER.685f680a-30b6-4dba-b9cb-2d621e24fcfe.tmp.xml
\\?\C:\ProgramData\Microsoft\Windows\WER\Temp\WER.c3c60740-abb8-4c04-a544-fc9711da9bfc.tmp.csv
\\?\C:\ProgramData\Microsoft\Windows\WER\Temp\WER.be8cc631-5df4-4bda-82e4-3258776b94d5.tmp.txt
\\?\C:\Users\Administrator\AppData\Local\Temp\WER.337ad876-47d6-44cc-b85a-e00a072bd446.tmp.appcompat.txt
\\?\C:\ProgramData\Microsoft\Windows\WER\ReportQueue\AppCrash_sqlservr.exe_7db2cb6f1a5bb53cb07826afa1c25735cec610_8a932130_cab_8a90fb42-c0d2-4726-8075-69ef4dfff944\memory.hdmp

可在此处获取这些文件:
\\?\C:\ProgramData\Microsoft\Windows\WER\ReportQueue\AppCrash_sqlservr.exe_7db2cb6f1a5bb53cb07826afa1c25735cec610_8a932130_cab_8a90fb42-c0d2-4726-8075-69ef4dfff944

分析符号: 
重新检查解决方案: 0
报告 ID: c1912614-645d-48cf-903b-aa73054de47b
报告状态: 100
哈希存储段: nCab GUID: 0

事件ID：528
来源：SQLLocalDB 15.0
事件数据：

Windows API 调用 WaitForMultipleObjects 返回了错误代码: 575。Windows 系统错误消息为: {应用程序错误}
应用程序无法正常启动(0x%lx)。请单击“确定”关闭应用程序。
，报告的行是: 3714. 

附上英文版的事件日志

Windows Event Logs

Windows API call WaitForMultipleObjects returned error code: 575. Windows system error message is: {Application Error} 
The application was unable to start correctly (0x%lx). Click OK to close the application. 
Reported at line: 3714.

解决方案

根据最近一条错误事件信息，又是几番搜索，
最终找到这一条冷门的解决方案：https://dba.stackexchange.com/questions/303823/error-starting-localdb-on-windows-11

文中提到：

Windows 11 and Windows Server 2022 do not report compatible PhysicalBytesPerSectorForAtomicity information for some SSD storage devices at this time. This causes issues with SQL Server IO when reported size is over 4K. See troubleshoot errors related to system disk sector size greater than 4 KB for additional details.

For your reference, below is example output of the command from a working (Samsung 980 PRO 2TB NVMe) and non-working (Samsung 980 1TB NVMe) system:fsutil fsinfo sectorinfo c:

Working drive:

LogicalBytesPerSector :                                 512
PhysicalBytesPerSectorForAtomicity :                    4096
PhysicalBytesPerSectorForPerformance :                  4096
FileSystemEffectivePhysicalBytesPerSectorForAtomicity : 4096

Problem drive:

LogicalBytesPerSector :                                  512
PhysicalBytesPerSectorForAtomicity :                   16384
PhysicalBytesPerSectorForPerformance :                 16384
FileSystemEffectivePhysicalBytesPerSectorForAtomicity : 4096

Does anyone have any recommendations for me to try?

Work-arounds I've seen suggested include:

• Install SQL Server on a drive that reports correct sector information (not over 4K)
• Create a VHD/VHDX and install SQL Server on that drive
• Start SQL Server with trace flag 1800

The trace flag work-around is probably the easiest for your existing installation. However, it doesn't seem LocalDb provides a documented way to specify trace flags (one can use SQL Server Configuration Manager for other editions). I found this answer on SO that shows the registry location for LocalDb startup parameters and tweaked it for SQL 2019 LocalDb and trace flag 1800.

I tested these Powershell commands on my PC and it sets the LocalDB 1800 trace flag correctly.

New-Item -Path 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15E.LOCALDB\MSSQLServer\Parameters' -Force
New-ItemProperty -Path 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15E.LOCALDB\MSSQLServer\Parameters' -Name 'SQLArg0' -Value "-T1800" -PropertyType String -Force

You'll need to restart localDb afterwards:

sqllocaldb stop MSSQLLocalDB
sqllocaldb start MSSQLLocalDB

感言

我尼玛，这冷门错误都给我碰上了！！！

附赠

有一网友微信问我：如何获取SQL Server最近几天执行过的SQL语句？

这里附上查询指定日期范围内的前1000条SQL执行记录：

SELECT TOP
	1000 QS.creation_time,
	SUBSTRING (
		ST.text,
		( QS.statement_start_offset / 2 ) + 1,
		(
		( CASE QS.statement_end_offset WHEN - 1 THEN DATALENGTH( ST.text ) ELSE QS.statement_end_offset END - QS.statement_start_offset ) / 2 
	) + 1 
	) AS statement_text,
	ST.text,
	QS.total_worker_time,
	QS.last_worker_time,
	QS.max_worker_time,
	QS.min_worker_time 
FROM
	sys.dm_exec_query_stats QS CROSS APPLY sys.dm_exec_sql_text ( QS.sql_handle ) ST 
WHERE
	1 = 1 
	AND QS.creation_time BETWEEN '2022-07-01 10:00:00' 
	AND '2022-07-31 23:59:59' 
	AND ST.text LIKE '%%' 
ORDER BY
	QS.creation_time DESC;