Cannot install SQL.. Any version

Question

This error is from an attempted install of SQL 2012, but 14 and 19 return some flavor of this error. Windows 10.

Cannot use file 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\Template Data\master.mdf' because it was originally formatted with sector size 4096 and is now on a volume with sector size 32768. Move the file to a volume with a sector size that is the same as or smaller than the original sector size.

Something that I feel MAY be relevant.. running fsutil fsinfo sectorinfo returns the following

C:\windows\system32>fsutil fsinfo sectorinfo c:
LogicalBytesPerSector : 512
PhysicalBytesPerSectorForAtomicity : 32768
PhysicalBytesPerSectorForPerformance : 32768
FileSystemEffectivePhysicalBytesPerSectorForAtomicity : 4096
Device Alignment : Aligned (0x000)
Partition alignment on device : Aligned (0x000)
No Seek Penalty
Trim Supported
Not DAX capable
Not Thinly-Provisioned

Only info I can find on this is an old post from 2012.. https://social.msdn.microsoft.com/Forums/en-US/5c090e72-e83d-4fca-a087-75a3df5d2b4f/localdb-cannot-start-strange-error-in-log-contains-quotsector-size-4096-and-is-now-on-a-volume?forum=sqlexpress

And that said there would be a BIOS update from Dell.. Well.. This is an HP.

My gut is telling me that the drive is reporting two different things and confusing SQL.. And that i'll likely get passed between HP and Samsung (It's a newer NVMe 991 drive from Samsung) and then kicked to Microsoft with everyone pointing fingers here. Feels like drive is the problem to me. but.. Tossing it out here to folks smarter than me. No BIOS updates for the HP, no firmware updates for the Samsung drive.

thoughts?

Answer 1

On SSDs the sector size is not real. It is simulated in the driver for backwards compatibility. There should be a way to tell it to report a reasonable number other than 32k either in the driver or via the command line.

Answer 2

Might be on to something here.. So.. Had to do some registry changes and now, I can see

LogicalBytesPerSector : 512
PhysicalBytesPerSectorForAtomicity : 4096
PhysicalBytesPerSectorForPerformance : 4096
FileSystemEffectivePhysicalBytesPerSectorForAtomicity : 4096
Device Alignment : Aligned (0x000)
Partition alignment on device : Aligned (0x000)
No Seek Penalty
Trim Supported
Not DAX capable
Not Thinly-Provisioned

I'm removing SQL and reinstalling, because after this, SQL still wouldn't start, but different errors and honestly I don't think it installed properly/fully the first go around anyway.

This page.. https://github.com/MicrosoftDocs/SupportArticles-docs/blob/main/support/sql/admin/troubleshoot-os-4kb-disk-sector-size.md

Has some info about the sector sizes.. Under Resolutions there's a registry entry that you can use to force it back to a 4k max sector size. I've added that, rebooted, and I get the above.

After uninstalling SQL, then reinstalling.. All is well.

Based on that other page, this seems to be an issue WIn11 specific and.. I'd expect alot of people to encounter this soon.

So.. Just in case. Pasting the registry entry here just in case that other site/post goes poof.

REG ADD "HKLM\SYSTEM\CurrentControlSet\Services\stornvme\Parameters\Device" /v "ForcedPhysicalSectorSizeInBytes" /t REG_MULTI_SZ /d "* 4095" /f