SQL SERVER – FIX: SQL Server Not Starting – Msg 864, Level 16, State 1- Buffer Pool Extension Feature

Recently I got an email from one of the student who attended my SQL Server 2014 Pluralsight Course from last year, he was trying to use Buffer Pool Extension feature and got into some unique trouble. Here is the email from him:

Hi Pinal,
I watched your course about SQL Server 2014 administration new features on PluralSight. It is really a great course for someone like me who is trying to do fast ramp-up on SQL Server 2014.

I was trying to experiment with the buffer pool extension feature. I have enabled the buffer pool extension feature on SQL Server Standard Edition and now I am not able to start my SQL Server service. Could you please help me out? This is not a business down situation, but want to understand what went wrong.

Regards,

(Name Hidden)

The very first thing which I asked him was to provide the error log information. Whenever there is a problem with the SQL startup, I always start looking at the Error log. Here is what I saw in the SQL Server Error Log.

2015-02-28 00:39:11.63 spid8s      Starting up database 'master'.
…
2015-02-28 00:39:11.84 spid8s      Attempting to allocate 6560086 BUF for buffer pool extension for a maximum of 6560085 page descriptors.
2015-02-28 00:39:11.84 spid8s      Error: 864, Severity: 16, State: 1.
2015-02-28 00:39:11.84 spid8s      Attempting to allocate 6560086 BUF for buffer pool extension for a maximum of 6560085 page descriptors.

Above ERRORLOG explains that, SQL server is not able to allocate the required buffers into the buffer pool extension file. If you look closely at both numbers, the difference of one buffer. This would mean that the size of buffer pool extension was more than the maximum allowed size. I looked into the documentation about the size limitation and found below link on MSDN.

https://msdn.microsoft.com/en-us/library/dn133176.aspx

(The buffer pool extension, size can be up to 32 times the value of max_server_memory for Enterprise editions, and up to 4 times for Standard edition)

I went back and looked into the error log and on the top found below.

Microsoft SQL Server 2014 - 12.0.2430.0 (X64)
Oct 15 2014 16:05:37
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

So it means that the size given for BPE file was going beyond the expected limit of 4 times of max server memory because of a standard edition.

So now, we know why the error is occurring and we are unable to start SQL Server. To fix the error we need to start SQL Server with minimum configuration so the BPE is not initialized. We can use startup parameter f. Here are the detailed steps.

1. Open command prompt. Use “Run As Administrator”, if applicable.
2. Type net start MSSQLServer /f /mSQLCMD

Since my machine has default instance, I am using MSSQLServer. For named instance, it would be MSSQL$<InstanceName>

We have given additional parameter called “m” and passed SQLCMD to let SQL Server know that only SQLCMD can connect.

3. Once SQL is started, connect to SQL via SQLCMD using below on command prompt.

SQLCMD -S(local) -E

Note that you need to provide your own server name. In the above example I am connecting to default instance of SQL.

4. Once connection is made, we need to disable BPE by using the below command

 ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF 

5. 5. Once it’s turned off, we can stop SQL by net stop command as below

Net Stop MSSQLServer 

6. Now we can start SQL normally and it should work (unless we have some other problem)

To dig further, I have taken a Virtual Machine of SQL Server Standard Edition in Microsoft Azure Cloud. My machine had below configuration:

RAM = 14 GB
Max Server Memory = 12 GB
D drive is SSD for BPE file. Over there I created folder SQLAuthority_BPE

Here are the steps to reproduce the error in SQL Server Standard Edition.

SP_CONFIGURE 'MAX SERVER MEMORY', 12000
GO
RECONFIGURE WITH OVERRIDE
GO
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON (FILENAME = 'D:\SQLAuthority_BPE\BUFFERPOOLEXT.BPE' ,SIZE = 50 GB)
GO
SELECT * FROM SYS.DM_OS_BUFFER_POOL_EXTENSION_CONFIGURATION
GO
SHUTDOWN WITH NOWAIT

Important: Above script would also shutdown SQL Server because I have added shutdown T-SQL command.

Once it was done, I was not able to start SQL without following the steps which I mentioned earlier. After fixing the problem, I configured BPE with 48 GB is size and it worked fine. Here is the message which we can see in ERRORLOG

2015-02-28 09:57:42.110 spid8s       Buffer pool extension "D:\BPE\BUFFERPOOLEXT.BPE" has been initialized successfully with size is 49152 MB.

Hope this would help in understanding the limitation and fixing the error which I have not seen so far.

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