SQL SERVER – Service Pack Error – The User Data directory in the registry is not valid. Verify DefaultData key under the instance hive points to a valid directory

As a good SQL Server DBA, I always keep updating my SQL Server on my all VMs with the latest service pack available. During my last installation of service pack 2 on SQL Server 2012, I got below error.

The User Data directory in the registry is not valid. Verify DefaultData key under the instance hive points to a valid directory.

I remember that I have played with some registry keys and now I realized that they are important keys. I looked into the logs based on MSDN articles and found below in Summary.txt file.

Detailed results:
Feature: Database Engine Services
Status: Failed: see logs for details
Reason for failure: An error occurred during the setup process of the feature.
Next Step: Use the following information to resolve the error, and then try the setup process again.
Component name: SQL Server Database Engine Services Instance Features
Component error code: 0x851A0043
Error description: The User Data directory in the registry is not valid. Verify DefaultData key under the instance hive points to a valid directory.

Then I looked in the Detail.txt and found below:

(01) 2015-05-13 09:19:09 SQLEngine: --SqlEngineSetupPrivate: <strong>Validating path:E:\MSSQL\Data </strong>
(01) 2015-05-13 09:19:09 Slp: Configuration action failed for feature SQL_Engine_Core_Inst during timing Validation and scenario Validation.
(01) 2015-05-13 09:19:09 Slp: The User Data directory in the registry is not valid. Verify DefaultData key under the instance hive points to a valid directory.
(01) 2015-05-13 09:19:09 Slp: The configuration failure category of current exception is ConfigurationValidationFailure
(01) 2015-05-13 09:19:09 Slp: Configuration action failed for feature SQL_Engine_Core_Inst during timing Validation and scenario Validation.
(01) 2015-05-13 09:19:09 Slp: Microsoft.SqlServer.Configuration.SqlEngine.ValidationException: The User Data <strong>directory in the registry is not valid. Verify DefaultData key under the instance hive points to a valid directory.</strong>
(01) 2015-05-13 09:19:09 Slp: at Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineSetupPrivate.CheckIfDirectoryExistsGeneric(String sqlPath, SqlEngineErrorCodes invalidError)
(01) 2015-05-13 09:19:09 Slp: at Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineSetupPrivate.ValidateDataRegistryKeys(EffectiveProperties properties)
(01) 2015-05-13 09:19:09 Slp: at Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineSetupPrivate.Patch(ConfigActionTiming timing, Dictionary`2 actionData, PublicConfigurationBase spcb)
(01) 2015-05-13 09:19:09 Slp: at Microsoft.SqlServer.Configuration.SqlConfigBase.SlpConfigAction.ExecuteAction(String actionId)
(01) 2015-05-13 09:19:09 Slp: at Microsoft.SqlServer.Configuration.SqlConfigBase.SlpConfigAction.Execute(String actionId, TextWriter errorStream)
(01) 2015-05-13 09:19:09 Slp: The following is an exception stack listing the exceptions in outermost to innermost order
(01) 2015-05-13 09:19:09 Slp: Inner exceptions are being indented
(01) 2015-05-13 09:19:09 Slp:
(01) 2015-05-13 09:19:09 Slp: Exception type: Microsoft.SqlServer.Configuration.SqlEngine.ValidationException
(01) 2015-05-13 09:19:09 Slp: Message:
(01) 2015-05-13 09:19:09 Slp: The User Data directory in the registry is not valid. Verify DefaultData key under the instance hive points to a valid directory.

I search for DefaultData key in registry and found at below location

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQLServer

RegistryData 01 SQL SERVER   Service Pack Error   The User Data directory in the registry is not valid. Verify DefaultData key under the instance hive points to a valid directory

As we can see, it is pointing to E:\MSSQL\Data and it was not valid. Later I played with various other keys and found below possible errors.

Invalid Values in Registry Error Message
DefaultData The User Data directory in the registry is not valid. Verify DefaultData key under the instance hive points to a valid directory.
DefaultLog The User Log directory in the registry is not valid. Verify DefaultLog key under the instance hive points to a valid directory.
SQLDataRoot The Database Engine system data directory in the registry is not valid.

SQLDataRoot in locating under HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\Setup

The cause of all errors was invalid path, which I have messed up. Later I search on the Microsoft site and found that there is a connect item filed by someone

https://connect.microsoft.com/SQLServer/feedback/details/778275/sql-server-2012-sp1-installation-issue

Moral of the story is never playing directly with the registry unless you are sure what they are. I was able to fix the error by correcting the value in DefaultData registry key.

Have you encountered such errors and were able to look at the logs?

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

SQL SERVER – WARNING – SQL Server Has Encountered N Occurrence(s) of I/O Requests Taking Longer Than 15 Seconds

This is one of the most commonly asked questions via blog comments and emails sent across to me. Here is the sample error message which has been reported in SQL Server ERRORLOG.

SQL Server has encountered 201 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [T:\MSSQL\DATA\tempdb.mdf] in database id tempdb [2].  The OS file handle is 0x0000000000000770.  The offset of the latest long I/O is: 0x0000000008c000

If you are not a SQL Expert and don’t know what ERRORLOG is, no need to worry. Here is the blog to check location of SQL Server ERRORLOG

SQL SERVER – Where is ERRORLOG? Various Ways to Find its Location

Coming back to the error message, the text which is highlighted in bold would vary based on the problem. The sample error message is for tempdb database which has database ID = 2. Path of the MDF file is T drive in the sample message. Some DBA also refer this as a stalled IO. In the sample test we are seeing 201 occurrences but I have seen value going till 5 digits also.

So, what’s the cause of the error message? Is there any problem with SQL Server? Most of the time answer is NO because SQL is reporting warning in Errorlog when it is not able to complete the IO in even in 15 seconds. If you have read any blog or book covering internals of SQL Server, you might know that SQL Server does asynchronous IO, using the win32 APIs like ReadFile, WriteFile, ReadFileScatter and WriteFileGather. One IO is posted, the thread would return and do meaningful work rather than waiting for IO to complete. The threads would keep checking the pending IO and report if they have taken longer.

Here are the thing which a DBA should do before going to hardware team.

1. Note the pattern of the message. If they are logged at any specific time? Check if there is any scheduled maintenance job is running at the same time? Rebuild index and other IO intensive tasks would perform a lot of IO and disk may report slowness at that time.

2. Check Event log (system and application) and check if there are any disk related error or warning messages.

3. Keep monitoring sysprocesses and check for waits which are happening for SPIDs. If the disk is slow all the times then you should see waits of PAGEIOLATCH, WRITELOG or LOGBUFFER with high wait time. Refer my earlier blogs.

LOGBUFFER Explanation

WRITELOG Explanation

 IO15Sec 01 SQL SERVER   WARNING   SQL Server Has Encountered N Occurrence(s) of I/O Requests Taking Longer Than 15 Seconds

4. Capture disk related data using peformance monitor (start > run > perfmon) for extended amount of time and have a look at counters. You should look at the performance counters related to storage performance to evaluate whether the numbers you are seeing are expected and within reasonable bounds.

Avg Disk Sec/Transfer: This counter is available under Logical Disk and Physical Disk object. The value of this counter should be less than 0.020 (=20 ms). Same counter is available for Read and Write also and can be used to find performance difference between reads and writes.

Disk Bytes/sec:  SAN’s generally start from 200-250 MB/s these days (note that the counter value is in bytes so we need to divide that by 1024). Same counter is available for read and write also.

Disk Transfers/sec: this counter represents Number of read and write performance also known as IOPS (I/O per second). This can be used to compare against capacity of storage subsystem. Read and write counter also available.

5. Make sure latest Windows patch has been applied to avoid any known issues with windows itself.

Once basics have been verified and we have confirmed that we are seeing symptoms of slow response from disk, hardware team need to be engaged for further investigation.

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

SQL SERVER – Backup Failure – Error: 3203, Severity: 16, State: 1

In the recent past, I have been writing about different error messages on SQL Server that one encounters. Thanks to my blog readers who have been sharing a lot of errors with me either by email or by blog comments and asking for solution. If I find something interesting and more helpful to other, I write a blog for that. This is one of such blog.

2015-05-05 10:00:00.440 Backup       Error: 3203, Severity: 16, State: 1.
2015-05-05 10:00:00.440 Backup       Read on “M:\MSSQL\TLog\Production_1.ldf” failed: 1(Incorrect function.)
2015-05-05 10:00:00.440 Backup       Error: 3041, Severity: 16, State: 1.
2015-05-05 10:00:00.440 Backup       BACKUP failed to complete the command BACKUP LOG ProductionDB. Check the backup application log for detailed messages.

When I was searching for other emails with error message, I found another one as below.

2014-01-05 16:07:38.19 Backup   Error: 3203, Severity: 16, State: 1.
2014-01-05 16:07:38.19 Backup   Read on “M:\DatabaseName_DefaultData.ndf” failed: 1117(failed to retrieve text for this error. Reason: 15100)

This error would vary based on the actual cause. In first error message, it is operating system error 1 and in second case, it is operating system error 1117. To convert any operating system error number to text, we can use windows net command (net helpmsg) from command prompt as shown below

nethelpmsg 01 SQL SERVER   Backup Failure   Error: 3203, Severity: 16, State: 1

If you notice the second message, we are not seeing the text of the message 1117 but we are seeing “failed to retrieve text for this error. Reason: 15100”

What you should do in case of such error?

You should start looking at system and application event log based in operating system error. The example error which I have shown above are due to hardware level issues. Here is what we found in event log:

Log Name: System
Source: Disk
Date: 5/5/2015 09:32:11 AM
Event ID: 51
Task Category: None
Level: Warning
Keywords: Classic
User: N/A
Computer: SQLServerMachine
Description: An error was detected on device \Device\Harddisk4\DR4 during a paging operation.

Here is an article which explained event ID 51 https://support.microsoft.com/en-us/kb/244780/

Have you ever seen any similar errors? Put into the comment section to help others and share your knowledge. The best thing about these errors is that we get to learn from each other interesting concepts.

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

SQL SERVER – Login Failed For User – Reason Server is in Script Upgrade Mode

I just can’t get enough of the error messages that land into my Inbox almost every single day. I do my due diligence of making a search in my blogs and I try to hang around in the forums from time to time to learn what people are getting as errors. While browsing through forums, I have been able to find that this error was faced by many users. It is one of the most common errors that seem to land, let me know if you ever faced the following error in your environments:

Login failed for user ‘MyDomain\Username. Reason: Server is in script upgrade mode. Only administrator can connect at this time. (.Net SqlClient Data Provider)

I did some more research and found below facts:

  • Whenever any SQL Server patch is applied, setup would patch the binaries first.
  • During the restart of instance, SQL Server startup would go though “script upgrade mode” during recovery phase.
  • Script upgrade mode is the phase where objects inside the databases are upgraded based on recently patch applied.
  • Based on features installed and number of databases available, it would take varying amount of time.

The best way to look at the upgrade script status is to keep observing ERRORLOG continuously. Since we would be not able to connect to SQL via SSMS or client tools, we need to open ERRORLOG via windows explorer by going to physical location. Once “Recovery is complete” message is printed in ERRORLOG, we should not see the message any further.

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

SQL SERVER – Where is ERRORLOG? Various Ways to Find its Location

Whenever someone reports some weird error on my blog comments or sends email to know about it, I always ask to share SQL Server ERRORLOG file. There have been many occasions where I need to guide them to find location of ERRORLOG file generated by SQL Server. Most DBA’s are intelligent and know some of these, but this is my try to share my learnings.

I decided to write this blog so that I can reuse it rather than sending steps every time. At this point I must point out that even if the name says ERRORLOG, it contains not only the errors but information message also. Here are various ways to find the location of SQL Server Errorlogs:

A) If SQL Server is running and we are able to connect to SQL Server then we can do various things. So we can connect to SQL Server and run xp_readerrorlog.

USE MASTER
GO
EXEC xp_readerrorlog 0, 1, N'Logging SQL Server messages in file'
GO

errorlog 01 SQL SERVER   Where is ERRORLOG? Various Ways to Find its Location

If you can’t remember above command just run xp_readerrorlog and find the line which says “Logging SQL Server messages”.

B) If we are not able to connect to SQL Server then we should SQL Server Configuration Manageuse. We need to find startup parameter starting with -e. Below is the place in SQL Server Configuration Manager (SQL 2012 onwards) where we can see them.

errorlog 02 SQL SERVER   Where is ERRORLOG? Various Ways to Find its Location

C) If you don’t want to use both ways then here is the little unknown secret. The ERRORLOG is one of startup parameter and its values are stored in registry key and here is the key in my server. SQLArg1 shows parameter starting with -e parameters which point to Errorlog file.

errorlog 03 SQL SERVER   Where is ERRORLOG? Various Ways to Find its Location

Here is the key which I highlighted in image: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014\MSSQLServer\Parameters\

Note that “MSSQL12.SQL2014” would vary based on SQL Server Version and instance name which is installed. Here is the quick table with version reference

SQL Server Version Key Name
SQL Server 2008 MSSQL10
SQL Server 2008 R2 MSSQL10_50
SQL Server 2012 MSSQL11
SQL Server 2014 MSSQL12

In SQL Server 2005, we would see a key name in the format of MSSQL.n (like MSSQL.1) the number n would vary based on instance ID.

Here is a key where we can get mapping of Instance ID and directory.

errorlog 04 SQL SERVER   Where is ERRORLOG? Various Ways to Find its Location

In the above image, you can see that this computer has a default instance (Instance Name MSSQLSERVER) of SQL Server 2012 and named instance (Instance Name SQL2014) of SQL Server 2014.

In case you are contacting me for any error, get the Errorlog using this blog.

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

SQL SERVER – Backup on mapped drive failing with error – Msg 3201, Level 16, State 1 – Operating system error 3(The system cannot find the path specified.)

We are so used to taking backups and it can sometimes give us new experiences. One of the common messages I see are around taking backups on a share. If you have found this blog while searching for error message then you may want to look at below blogs as well if you are NOT dealing with mapped drive.

SQL SERVER – Error: Fix: Msg 5133, Level 16, State 1, Line 2 Directory lookup for the file failed with the operating system error 2(The system cannot find the file specified.) – Part 2

This time I will explain why you are getting this error while taking backup on a mapped drive. Here is the situation:

  1. SQL Server Service running under domain account.
  2. You have already mapped the drive in Windows and can see that drive in Windows Explorer.
    bkp map drive 01 SQL SERVER   Backup on mapped drive failing with error   Msg 3201, Level 16, State 1   Operating system error 3(The system cannot find the path specified.)
  3. You are not able to see the drive which is mapped. Below is the screenshot of screen where you choose backup file name. If you try to take backup from SQL Server Management Studio (SSMS) you are not able to see the drive. As we can see, SSMS is only showing local drive (C and D)
    bkp map drive 02 SQL SERVER   Backup on mapped drive failing with error   Msg 3201, Level 16, State 1   Operating system error 3(The system cannot find the path specified.)
  1. Backup command is failing with below error.

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘Z:\master.bak’. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

bkp map drive 03 SQL SERVER   Backup on mapped drive failing with error   Msg 3201, Level 16, State 1   Operating system error 3(The system cannot find the path specified.)

If we try to get details about the drives visible from SQL Server by Windows “dir” command then we see below.

xp_cmdshell 'dir Z:'

output
———————————————–
The system cannot find the path specified.
NULL
(2 row(s) affected)

Above confirms that SQL is not able to see Z drive, even though it is mapped on Windows. In case you get below error

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 72
The configuration option ‘xp_cmdshell’ does not exist, or it may be an advanced option.

Then you need to execute below to enable xp_cmdshell as its disabled by default due to security reasons. (Please turn off again once you done with the work)

EXEC sp_configure 'advanced', 1
RECONFIGURE WITH override
GO
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH override
GO

We are seeing “The system cannot find the path specified.” because SQL Server instance as it is running as a service. To fix the error, we need to map the drive via SQL Server. Here are the steps.

-- Map the drive via T-SQL so that SQL can see it.
EXEC xp_cmdshell 'net use Z: \\BigPinal\SharedFolder'
GO

bkp map drive 04 SQL SERVER   Backup on mapped drive failing with error   Msg 3201, Level 16, State 1   Operating system error 3(The system cannot find the path specified.)

Now we can check mapped drive via T-SQL.

xp_cmdshell 'dir Z:'

bkp map drive 05 SQL SERVER   Backup on mapped drive failing with error   Msg 3201, Level 16, State 1   Operating system error 3(The system cannot find the path specified.)

You may want to add more parameters for net use. Refer Net use documentation

Once this is done, we can see that drive in SSMS as shown below

bkp map drive 06 SQL SERVER   Backup on mapped drive failing with error   Msg 3201, Level 16, State 1   Operating system error 3(The system cannot find the path specified.)

… and as expected, backup command would not fail.

Have you ever faced this situation such weird situations? What were your resolution steps? Let me know as we can learn from each other.

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

SQL SERVER – Error – Msg 6401, Level 16: Cannot roll back Transaction

In a recent Usergroup meet that I attended someone asked me this simple yet interesting question about how transactions work and is there any concept like nested transactions inside SQL Server. Though I have talked about this in the past, thought it would be worth revisiting this concept to drive home some learnings again to readers who are new to SQL Server. Let us learn this using the script code as shown below:

-- Create our database for testing
USE MASTER;
GO
CREATE DATABASE TransactionsDemo;
GO
USE TransactionsDemo;
GO
-- Create a table for testing
CREATE TABLE tbl_SQLAuth (Col1 INT);
GO
SET NOCOUNT ON;
GO

Once the database is created, our next logical step is to create some transactions to test if we can mimic the use of nested transactions.

-- Create an explicit transaction
BEGIN TRAN OuterTransaction;
GO
INSERT INTO tbl_SQLAuth VALUES (1);
GO 10
-- Create an explicit so called nested transaction
BEGIN TRAN InnerTransaction;
GO
INSERT INTO tbl_SQLAuth VALUES (2);
GO 10
-- Look at the trancount?
SELECT @@TRANCOUNT;
GO

In the example above we started using an explicit transaction called as “OuterTransaction” and then inserted values post which we created yet another transaction called as “InnerTransaction”. The @@TRANCOUNT will show a value of 2 now. Now that we know the transaction count is 2, let us try to rollback the inner transaction alone.

-- Rollback the inner transaction
ROLLBACK TRAN InnerTransaction;
GO

Msg 6401, Level 16, State 1, Line 33
Cannot roll back InnerTransaction. No transaction or savepoint of that name was found.
Now this is interesting to see the error, we have explicitly created a transaction called as “InnerTransaction” in our code and it is getting ignored.

-- Works?
ROLLBACK TRAN OuterTransaction;
GO

Or the below command works:

-- Works?
ROLLBACK TRAN;
GO

And it is so strange that this above commands worked. If you try to query the table, it will have no rows.

-- What hapenned?
SELECT COUNT(*) FROM tbl_SQLAuth;

This is an important learning one needs to know when working with ROLLBACK command inside SQL Server. There is nothing like a nested transaction perse inside SQL Server. Go ahead and do the clean up.

-- Cleanup time
USE MASTER;
GO
DROP DATABASE TransactionsDemo;
GO

I am curious to know from developers out there, have you ever faced this situation in your environment and learnt it the hard way? If you can share your learning, it would be great.

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

SQL SERVER – FIX – A connection cannot be made to redirector. Ensure that ‘SQL Browser’ service is running. (Microsoft.AnalysisServices.AdomdClient)

Life gives you learning lessons from unexpected times, here is one such experience that I felt is worth sharing. It may be a surprise to my blog readers that I also have SQL Server Analysis Services installed on my primary machine. I use it occasionally because most of the time I work with the SQL Server Database engine. One fine day, I opened SQL Server Management Studio and instead of database engine, I selected analysis services (this was a mistake) and pressed on the connect button. Below is the error which I received:

TITLE: Connect to Server
——————————
Cannot connect to BIGPINAL
——————————
ADDITIONAL INFORMATION:
A connection cannot be made to redirector. Ensure that ‘SQL Browser’ service is running. (Microsoft.AnalysisServices.AdomdClient)
——————————
No connection could be made because the target machine actively refused it 127.0.0.1:2382 (System)
——————————
BUTTONS:
OK
——————————

This is the first time I saw such error while connecting to SQL Server, but soon I realized that I was trying to connect to SQL Server Analysis Services. I could have left it there because I don’t use it, but I did not. Since there was a little time, I thought it is worth solving this challenge in front of me. Here is the step by step resolution of what I did:

  1. Made sure that SQL Server Analysis Service was up and running. This got confirmed from my SQL Server Configuration Manager.
  2. As I do always, read the error message again and verified that the SQL Browser service was up and running.
  3. Since this was a local connection, I turned off the firewall as well.

None of the above solved the problem. The Port resolution is done by the SQL browser service in case of SQL Server connectivity. So I thought that it could be a permission issues – just a guess based on the error message.

I noticed that SQL Browser was running with the local system account. I found on internet that changing account helped many. So I used SQL Server Configuration Manager to change service account for the SQL Server Browser. I have used like below.

browser ssas 01 SQL SERVER   FIX   A connection cannot be made to redirector. Ensure that SQL Browser service is running. (Microsoft.AnalysisServices.AdomdClient)

Here are the steps:

  • Open SQL Server Configuration Manager
  • Right click “SQL Server Browser” and choose “Properties”
  • In the “Log On” Tab select “Local System” under “Built-In” account.

Have you ever seen such error and found some other way to fix it? Please comment and let others also know. The best way to learn something is by sharing.

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

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 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 Errorlog. 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 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.

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

  1. 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 above example I am connecting to default instance of SQL.

  1. Once connection is made, we need to disable BPE by using below command
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION OFF

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

  1. Once it’s turned off, we can stop SQL by net stop command as below
Net Stop MSSQLServer

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

  1. 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 (http://blog.sqlauthority.com)

SQL SERVER – FIX – Error: 905, Severity: 21, State: 1 – Database ‘xxx’ cannot be started in this edition of SQL Server because it contains a partition function ‘xxx’.

Recently one of my blog reader contacted me via mail and told that after upgrading from Enterprise Evaluation Edition to Standard Edition, the database was not accessible. To troubleshoot, I have asked them to share SQL Server ERRORLOG. There are various ways to read Errorlog. Here is the method to read Errorlog:

  • In Object Explorer, expand a server, expand Management, and then expand SQL Server Logs.
  • Right-click a log and click View SQL Server Log.

Other ways to get Errorlog are explained in detail by Balmukund (b|t) on his blog (Help : Where is SQL Server ErrorLog?)

In the Errorlog, I asked him to look for any error related to the database which is not accessible and soon they shared below messages:

2015-02-20 13:47:36.65 spid7s      Error: 905, Severity: 21, State: 1.
2015-02-20 13:47:36.65 spid7s      Database ‘My_Database’ cannot be started in this edition of SQL Server because it contains a partition function ‘myRangePF1′. Only Enterprise edition of SQL Server supports partitioning.
2015-02-20 13:47:36.65 spid7s      Error: 933, Severity: 21, State: 1.
2015-02-20 13:47:36.65 spid7s      Database ‘My_Database’ cannot be started because some of the database functionality is not available in the current edition of SQL Server.

Explanation

From the error we can see that the database is using partition function which is enterprise only feature. Here is the link which has edition comparison.  The database contains one or more partitioned tables or indexes. Standard edition of SQL Server cannot use partitioning. Therefore, the database cannot be started correctly. Partitioned tables and indexes are not available in every edition of Microsoft SQL Server. Books online topic “Features Supported by the Editions of SQL Server 2012” has complete list of feature.

Solution

I asked him to restore the database MDF and LDF files to another instance which is either developer, enterprise or enterprise evaluation edition. Then get rid of the features which are enterprise only feature. They can easily be found using the DMV sys.dm_db_persisted_sku_features

USE <DatabsaeName> -- Change the name to the database where we need to check
GO
SELECT *
FROM sys.dm_db_persisted_sku_features

Note that the query has to be run in the database which is under question, not the master database. Here are the possible values on SQL Server 2014.

  • TransparentDataEncryption
  • Compression
  • ChangeCapture
  • ColumnStoreIndex
  • InMemoryOLTP
  • Partitioning

Once the object referring to that feature is removed, the backup can be taken and restored on “lower” edition of SQL Server.

Here is another error which can be received while restoring database using compression

Msg 909, Level 21, State 1, Line 1
Database ‘SqlAuthority’ cannot be started in this edition of SQL Server because part or all of object ‘PageCompressionTest’ is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.

I always advice to run the DMV and check the features whenever there is a downgrade of edition. Even though evaluation to standard is an upgrade but I would call it as downgrade because there are few feature of enterprise editions would not be available in standard edition.

Have you ever encountered any downgrade scenarios and got some errors?

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