I ran into an odd Azure issue today and wanted to share it because I wish more people knew about it.
A lot of the confusion comes from mixing up sector size and NTFS allocation unit size. They are completely different things.
Think of a drive as a collection of boxes. When Windows saves data, it writes to those boxes. If only a small part of the data changes, the storage system still has to read the entire box, update the part that changed, and write the whole box back. A sector is the smallest box a disk can write atomically.
Most disks use 4 KB sectors. Newer Azure NVMe storage can report 8 KB sectors. Windows Server 2022 and Windows 11 show the actual physical sector size instead of emulating 4 KB. Sector size is a property of the storage device, and formatting does not change it.
SQL Server uses 8 KB pages. On a 4 KB sector disk, a page spans two sectors, which means a torn page is possible if a write is interrupted. SQL Server also has a hard limit on supported sector sizes. It supports only 512-byte and 4,096-byte sectors.
If a volume reports an 8 KB sector size, SQL Server will not use it.
You can see error 5178 when a file was created with a 4 KB sector size and the volume later reports 8 KB. Error 5179 is the other common one:
Cannot use file ‘…’, because it is on a volume with sector size 8192. SQL Server supports a maximum sector size of 4096 bytes.
A lot of people assume these are related, but sector size and the 64 KB allocation unit size recommendation are completely different things.
Sector size comes from the storage device. Allocation unit size is chosen when you format NTFS. For SQL Server data volumes, Microsoft recommends a 64 KB allocation unit size so an extent (8 pages) fits within a single cluster. That recommendation applies whether the disk uses 512-byte or 4 KB sectors. It does not make 8 KB sectors supported.
For Azure VMs, anything you need to keep should live on Premium SSD v2 or Ultra Disk. Data, log, and tempdb should each have their own disk.
Local NVMe is still a great option for tempdb because of the performance, but only after addressing the sector size issue. Older Premium SSD v1 storage on older VM sizes typically reports 4 KB sectors, so you usually will not run into this problem. It shows up most often on newer v6 VM series with local NVMe storage, and occasionally on Premium SSD v2 and Ultra Disk when the host reports 8 KB sectors.
I ran into this after changing the VM size and turning the server back on. I could not start the SQL Server services. After looking into it, I found the volume was reporting an 8 KB sector size, and SQL Server simply would not use it.
If a volume reports 8 KB sectors and you do not force a 4 KB sector size, SQL Server will not use the disk. This does not apply when you deploy a SQL Server VM image from Azure Marketplace because those images are already configured to use a supported 4 KB sector size.
Before you spend hours blaming SQL Server, check the sector size:
fsutil fsinfo sectorinfo C:If PhysicalBytesPerSectorForAtomicity reports 8192, you’ve found the problem.
Microsoft’s fix is to set the ForcedPhysicalSectorSizeInBytes registry value to * 4095, which causes the NVMe driver to report a 4 KB sector size instead of 8 KB.
The lesson here is to pay attention to both the VM type and the storage type. I changed an instance size, brought the server back online, and suddenly SQL Server would not start. The problem was not SQL Server at all. It was the sector size being reported by the storage.
Microsoft has a great article on how to solve it here:
Leave a Reply