If you’ve run into an issue with LocalDB or SQL Server where the process fails to start and you see an error message about misaligned log IOs, you’re not alone. This issue can be particularly frustrating, especially if you’re not sure where to start in terms of troubleshooting. Here’s what I did to fix the problem and get LocalDB working again.
First things first, you’ll want to check the SQL Server log files to see if there are any clues as to what might be causing the issue. If like me you notice a message stating something like “there have been 256 misaligned log IOs which required falling back to synchronous IO”, you’re in luck. Well, you’re in luck in the sense that you’re reading the right article and hopefully we can get you out of this mess. If you don’t see this message, but something entirely different, sorry friend, it’s back to googling for you.
Ok, here’s what’s most likely going on. You have a modern SSD and you either:
- Tried installing SQL Server (or localDB) on a Windows 11 machine.
- Installed SQL Server (or localDB) on a Windows 10 machine, then upgraded to Windows 11.
Maybe you’re running into problems trying to install SQL Server or just starting it up. Either way, if you’re seeing the “misaligned log IOs” error message, the root cause is the same: Windows 11 doesn’t behave the same way as Windows 10 when it comes to disk sector sizes on modern SSDs.
You want to know for sure if that is your problem? Run this command from the terminal:
fsutil fsinfo sectorinfo <volume pathname>Look for the values PhysicalBytesPerSectorForAtomicity and PhysicalBytesPerSectorForPerformance, returned in bytes, and if they’re different, retain the largest one to ascertain the disk sector size. If it’s bigger than 4096, bingo! That’s your problem. SQL server doesn’t like it when the sector size is bigger than 4096.
After a bit of googling, I eventually came across an article that talked about how to troubleshoot OS 4KB disk sector size issues. For more details as to the root cause of this issue, I’d recommend reading the article.
The article recommended a few different options to fix the problem. But in the end, if you want localDB to work and not only SQL Server, the only one that will probably end up working for you is to add a key value pair to the Windows registry. That’s what worked for me.
From PowerShell, as an administrator, run the following command:
New-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Services\stornvme\Parameters\Device" -Name "ForcedPhysicalSectorSizeInBytes" -PropertyType MultiString -Force -Value "* 4095"And after that, validate that the key was added to the registry:
Get-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Services\stornvme\Parameters\Device" -Name "ForcedPhysicalSectorSizeInBytes"Once that is done, you’ll probably need to restart your computer. After that, you should be able to start up SQL Server or LocalDB without any issues. If localDB still won’t start, you might just need to delete the localDB instance and recreate it by doing:
Sqllocaldb delete mssqllocaldb
Sqllocaldb create mssqllocaldb -sHopefully, this will work for you. I spent a good chunk of time trying to figure out what was going on and finding relevant information to fix this was not exactly easy and bits and pieces were scattered all over the place. Hopefully, this article will help someone else out there who is running into the same issue.