Error starting localDb on Windows 11

Question

I installed Windows 11 on a brand new NUC, then installed all updates. Installed Visual Studio 2022 CE and SSMS (v 18.10)

I attempted to start the localDB which is assume was installed as part of Visual Studio 2022/ SSMS and when entering (localdb)\mssqllocaldb in SSMS it get the error 

Cannot connect to (localdb)\mssqllocaldb.

SQL Network Interfaces, error: 50 - Local Database Runtime error occurred. 

Error Number: -1983577846

I then verified/attempted to start the instance as below

sqllocaldb stop mssqllocaldb
sqllocaldb delete mssqllocaldb
sqllocaldb start "MSSQLLocalDB"

When typing the last command i received the error 

Start of LocalDB instance "MSSQLLocalDB" failed because of the following error: Cannot create an automatic instance. See the Windows Application event log for error details.

I looked at the log file found under

....\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\MSSQLLocalDB

and see a few error files and select the most latest one by date.

It contains info and the last line is 

2021-12-11 10:48:42.67 spid10s     There have been 256 misaligned log IOs which required falling back to synchronous IO.  The current IO is on file C:\Users\....\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\MSSQLLocalDB\master.mdf.

The file is pretty short but i dont see an entry with Error anywhere and most lines end with This is an informational message; no user action is required.

Windows log states

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.

Opened up services and can see SQL Services VSS Writer with status of running and no other SQL service.

Does anyone have any recommendations for me to try?

Answer 1

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 fsutil fsinfo sectorinfo c: command from a working (Samsung 980 PRO 2TB NVMe) and non-working (Samsung 980 1TB NVMe) system:

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