SQL Server : installation fails with error code 0x851A001A – Wait on the Database Engine recovery handle failed

Question

Details:

• SQL Server 2017 (Developer or Express edition)
• Windows 2011 OS

I have followed this article already but no avail https://blog.sqlauthority.com/2017/01/27/sql-server-sql-installation-fails-error-code-0x851a001a-wait-database-engine-recovery-handle-failed/

  Feature:                       Database Engine Services
  Status:                        Failed
  Reason for failure:            An error occurred during the setup process of the feature.
  Next Step:                     Use the following information to resolve the error, uninstall this feature, and then run the setup process again.
  Component name:                SQL Server Database Engine Services Instance Features
  Component error code:          0x851A001A
  Error description:             Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.
  Error help link:               https://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=14.0.1000.169&EvtType=0xD15B4EB2%400x4BDAF9BA%401306%4026&EvtType=0xD15B4EB2%400x4BDAF9BA%401306%4026

Leastwise

And what does the SQL Server error log say, and what does the Windows Event log say? There are known issues with SQL Server on Windows 11, regarding file system block sizing, have you looked into that – Salleysalli

SO is for programming questions – Elviraelvis

I am guessing the OS you mean is Windows 11. If yes, then please correct it in your post otherwise it might confuse others. – Dogmatics

Answer 1

WHAT IS THE CAUSE?

this is caused by the sector size of the disks.

During service startup, SQL Server begins the database recovery process to ensure database consistency. Part of this database recovery process involves consistency checks on the underlying filesystem before attempting the activity of the opening system and user database files.

On systems running Windows 11, some new storage devices and device drivers will expose a disk sector size greater than the supported 4 KB sector size.

When this occurs, SQL Server will be unable to start due to the unsupported file system as SQL Server currently supports sector storage sizes of 512 bytes and 4 KB.

You can confirm that you encounter this specific issue by running the command:

fsutil fsinfo sectorinfo E:

Look for the value PhysicalBytesPerSectorForAtomicity, returned in bytes. A value of 4096 indicates a sector storage size of 4 KB.

HOW TO FIX IT!

Simply follow the instructions on this page.

https://learn.microsoft.com/en-us/troubleshoot/sql/admin/troubleshoot-os-4kb-disk-sector-size#resolutions

if you don't want to change the os, you should try this resolution on the above site.

You can add a registry key which will cause the behavior of Windows 11 and later to be similar to Windows 10. This will force the sector size to be emulated as 4 KB in size. To add the ForcedPhysicalSectorSizeInBytes registry key, use the Registry Editor, or you can run one of the following commands in Windows command prompt or PowerShell, executed as an administrator.

After you change the registry, you must restart the device and then reinstall the program. Otherwise, this method will not work!

Corr

Nice find. This issue also exists in SQL2022 Preview. And following the microsoft guide gets rid of the issue. Thanks – Dogmatics

what if i have 4kb PhysicalBytesPerSectorForAtomicity and problem still exists? – Eartha

Answer 2

1- Run the below command on Command Prompt as Administrator

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

2- Uninstall Component --> "SQL Server Database Engine Services"

3- then Reinstall Component again --> "SQL Server Database Engine Services"

Ettieettinger

Answer 3

please run these command in CMD as administrator then reset your windows and install SQL again.

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

REG QUERY 
"HKLM\SYSTEM\CurrentControlSet\Services\stornvme\Parameters\Device" /v 
"ForcedPhysicalSectorSizeInBytes"

Perfumer

Please provide some context and explanation for these commands in your answer. – Honshu