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