SQL SERVER – SQL Express Installation Error – Wait on the Database Engine Recovery Handle Failed

Sometimes I get emails from software vendors who use SQL Server as a part of their product. One of such vendor contacted me for an issue where their software install was failing because SQL Express was not getting installed. It was failing with an error. The error message which they shared was “Wait on the Database Engine recovery handle failed”.

I asked to share complete setup Logs to get a better understanding about the situation. I found below in Detail.txt file.

Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineConfigException: Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.
at Microsoft.SqlServer.Configuration.SqlEngine.SqlServerServiceBase.WaitSqlServerStart(Process processSql)
at Microsoft.SqlServer.Configuration.SqlEngine.SqlServerServiceSCM.StartSqlServer(String[] parameters)

This error generally occurs when a SQL service is not getting started via SQL Setup. When I looked into SQL ERRORLOG, here is the main issue which was causing the failure of SQL startup.

2017-08-29 12:51:27.05 Server Failed allocate pages: FAIL_PAGE_ALLOCATION 1

I had no idea why SQL is not getting started because there was no memory issue with the server.

We tried changing max server memory by starting SQL in single user mode and minimal configuration but there was no luck in getting SQL service normally. Every time we start, we were getting same memory error. I was clueless!

I was looking at more details about their SQL instance and I noticed that they are using Express edition of SQL Server. I looked at ERRORLOG again and found something interesting.

2017-08-29 12:51:27.05 Server
Memory node Id = 0 KB
—————————————- ———-
VM Reserved 34554780
VM Committed 8191988
Locked Pages Allocated 0
Pages Allocated 23800
Pages Free 7993056
Target Committed 8191976
Current Committed 8191992
Foreign Committed 8
Away Committed 0
Taken Away Committed 7993056

2017-08-29 12:51:27.05 Server
Memory node Id = 1 KB
—————————————- ———-
VM Reserved 0
VM Committed 20
Locked Pages Allocated 0
Pages Allocated 0
Pages Free 0
Target Committed 24
Current Committed 24
Foreign Committed 0
Away Committed 7993056
Taken Away Committed 0

As we can see above, there are two NUMA nodes available on this machine. We looked into the documentation from Microsoft and found below which says that express edition can use only 1 CPU only. https://msdn.microsoft.com/en-us/library/cc645993(v=sql.110).aspx

SOLUTION/WORKAROUND

I found that we need to use trace flags mentioned here and here

We added trace Flags: 8048 and 8015 as a startup parameter. If you want to step to enable a trace flag via startup parameter, you can refer my earlier blog on same topic.

SQL SERVER – What is Trace Flag – An Introduction

Later I also found KB which doesn’t tell about exact issue, but same symptoms and trace flag was the workaround.

Reference: Pinal Dave (https://blog.sqlauthority.com)