SQL SERVER – FIX: Msg 3169, Level 16, State 1 – The database was backed up on a server running version. That version is incompatible with this server.

At this rate, I think I am going to exhaust the whole error set available inside SQL Server. I am glad that I am able to get into unique situations and then resolve them too. And this is more of a diary of the error messages I am getting into. I was playing with my Demo database on SQL Server 2016. Once done, I started restore from the previous copy.  But is failed with below error:

Msg 3169, Level 16, State 1, Line 1
The database was backed up on a server running version 13.00.0801. That version is incompatible with this server, which is running version 12.00.4213. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Then I realized that I have taken back-up from 13.00.0801 which is SQL Server 2016 and I was trying to restore on 12.00.4213 which is SQL Server 2014. Error message shows both versions, source and destination.

So, it’s is clear that there is no direct way to downgrade from a higher version to a lower version and it would fail with error message. Why? When an instance of SQL is upgraded, not only do the binaries for the database engine change, the schema level for the databases also changes. So it would not be possible to attach a database with a higher schema level to an instance on a lower version of SQL.

What can be done? One possible approach is to manually export and import the data. You can follow the below steps:

Create empty database

  • In Object Explorer, expand Databases, right-click a database, point to Tasks, and then click Generate Scripts. Follow the steps in the wizard to script the database objects.
  • On the Choose Objects page, select “Script entire database and database objects”.
  • On the Set Scripting Options page, select Save scripts to a specific location.
  • select the Advanced button; under “Types of data to script” select “Schema Only” and under “Script for server version” select “SQL Server 2008” (or appropriate version)
  • Click on Next and finish to complete the process.
  • Now you can use the generated script file and execute this against the SQL server 2008 instance to create the complete database schema without data.

Move the data

To move the data, you have a couple of options but all of these are a bit tricky.

  1. Script out the data exactly the way described in the above steps, just by selecting “Types of data to script” as “Data Only”.

Or

  1. Script out the data for every object individually one by one instead of complete database if you have a large database.

Or

  1. BCP to export the data into files and then again use BCP to import the data back into the tables on to the new server.

In short, moving back-up from higher to lower version is not possible. Only option we have is to move the data using the ways described above. Do you know any more ways?

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

SQL SERVER – SSMA Error: System.Data.OracleClient requires Oracle client software version 8.1.7 or greater

I work majorly on SQL Server but rarely do I get a chance to work on Oracle in general. When some of the error messages reach my inbox, I get intrigued to why some occur. This error message was an outcome of that exploration. When I got this error message, I said I have no clue to why this is happening and I asked if it was anyway related to SQL Server?

The gentleman wrote back stating this was coming from a SQL Server tool and since they were doing a migration using SQL Server Migration Assistant Tool (SSMA). Now that statement got me interested because it was coming from a SQL Server migration.

I went ahead to asked about the environment to get a better hang of things. I asked the Oracle client version they were running. They responded saying, though we are running Oracle Client 9.2.0.8 we are getting an Oracle 8.1.7 or higher error strangely.

Following message is being displayed on the console:

Migrating data…
Analyzing metadata…
Preparing table SYSTBL.CMN_TBL_CUST_…
Preparing data migration package…
Starting data migration agent…
Starting data migration…
Exception during processing: System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.
Data migration complete.

0 table(s) successfully migrated.
0 table(s) partially migrated.
1 table(s) failed to migrate.

After a going through the complete problem statement. I saw that it was working on a local server, but was failing when it was called from remote server.

Reason After Investigation

The Oracle client provider is not there on the remote SQL Server. If working with remote machines, the Oracle Client provider should be present on the remote SQL Server.

I sent a mail to – Install the Oracle Client provider on the remote SQL Server and verify the data migration. As SSMA doesn’t have any limitation to work with remote SQL Server. Also as part of the installation, asked to check if the extension packs were installed on the remote servers.

After this exercise, I felt there was some good learning for me too and worth a share here. I am sure you are much more experienced in working with SSMA but feel free to let me know if I missed anything.

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

SQL SERVER – FIX: sp_rename error Msg 15225 – No item by the name of ‘%s’ could be found in the current database

Few days back I have written blog containing script for removing space in column name using sp_rename. In case you missed, I am listing them below for quick reference.

SQL SERVER – Script: Remove Spaces in Column Name in All Tables

SQL SERVER – How to Rename a Column Name or Table Name

While playing with sp_rename, I came across an interesting error message and I looked into the code of sp_rename to understand the issue. Here is the error message which I received.

Msg 15225, Level 11, State 1, Procedure sp_rename, Line 387
No item by the name of ‘TableTwo’ could be found in the current database ‘SQLAuthority’, given that @itemtype was input as ‘(null)’.

For simplification, I have made up below script to explain the error.

CREATE DATABASE SQLAuthority
GO
USE SQLAuthority
GO
CREATE SCHEMA HR
GO
CREATE TABLE dbo.TableOne (ID INT PRIMARY KEY)
GO
CREATE TABLE HR.TableTwo (ID INT, FName VARCHAR(100))
GO
sp_rename 'TableOne', 'TableOne_renamed' -- This works
GO
sp_rename 'TableTwo','TableTwo_renamed' -- This fails

If we look at sp_helptext sp_rename we would see below at line 387 (as shown in error)

-- was the original name valid given this type?
IF (@objtype IN ('object','userdatatype') AND @CountNumNodes > 3)
BEGIN
COMMIT TRANSACTION
RAISERROR
(15225,-1,-1,@objname, @CurrentDb, @objtypeIN)
RETURN 1
END

I looked at beginning of sp_rename the stored procedure and found some documentation there, there were some good rules listed.

  • To rename a table, the @objname (meaning OldName) parm can be passed in totally unqualified or fully qualified.
  • The SA or DBO can rename objects owned by lesser users, without the need for SetUser.
  • The Owner portion of a qualified name can usually be passed in in the omitted form (as in MyDb..MyTab or MyTab). The typical exception is when the SA/DBO is trying to rename a table where the @objname is present twice in sysobjects as a table owned only by two different lesser users; requiring an explicit owner qualifier in @objname.
  • An unspecified Owner qualifier will default to the current user if doing so will either resolve what would otherwise be an ambiguity within @objtype, or will result in exactly one match.
  • If Database is part of the qualified @objname, then it must match the current database. The @newname parm can never be qualified.
  • Here are the valid @objtype values. They correspond to system tables which track each type: ‘column’  ‘database’  ‘index’  ‘object’  ‘userdatatype’  ‘statistics’
  • The @objtype parm is sometimes required. It is always required for databases.  It is required whenever ambiguities would otherwise exist.  Explicit use of @objtype is always encouraged.

Parms can use quoted_identifiers.  For example: Execute sp_rename ‘amy.”his table”‘,'”her table”‘,’object’

So, we are getting error because our table TableTwo is not in the default schema which is dbo. The solution is very simple: we need to qualify the name of the table with the schema name as shown below.

sp_rename 'HR.TableTwo','TableTwo_renamed' -- Now This would work

While reading the ruled listed in stored procedure, I realized that same error can also come if there is a dot in table name. we need to use [] around such tables. Here is an example

USE SQLAuthority
GO
CREATE TABLE [Name.With.dot] ( i INT)
GO
sp_rename 'Name.With.dot', 'New_Name.With.dot' -- would fail
GO
sp_rename '[Name.With.dot]', 'New_Name.With.dot' -- would work
GO

Hope this would help. Do let me know if you have used something like this before in your environments. Feel free to share via comments below.

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

SQL SERVER – FIX: SQLServerAgent is not currently running so it cannot be notified of this action. (Microsoft SQL Server, Error: 22022)

Getting error and finding the cause is something which I like. And off late over the blogs I have shared a number of such incidents that have helped me figure out stuffs all by myself. Of course, I do my research and more often than not make things complicated because there are a number of resolutions that I find. I also make it a point to check with a few friends from Microsoft to a possible cause because it is easy to get help than walk the tough line of finding it myself.  One fine day I restarted my virtual machine and then I wanted to take a full backup of all databases before formatting the machine. As soon as I hit on execute button, as shown below, I was welcomed with an error message.

Agent Not Running 01 SQL SERVER   FIX: SQLServerAgent is not currently running so it cannot be notified of this action. (Microsoft SQL Server, Error: 22022)

Here is the error which I received.

Agent Not Running 02 SQL SERVER   FIX: SQLServerAgent is not currently running so it cannot be notified of this action. (Microsoft SQL Server, Error: 22022)

Complete text of the error message is below:

TITLE: Execute Maintenance Plan
——————————
Execution failed. See the maintenance plan and SQL Server Agent job history logs for details.
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
SQLServerAgent is not currently running so it cannot be notified of this action. (Microsoft SQL Server, Error: 22022)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.2480&EvtSrc=MSSQLServer&EvtID=22022&LinkId=20476
——————————
BUTTONS:
OK
——————————

Error message is very clear. SQL Server Agent service should be running before we can execute maintenance plan. If we notice the first screen shot, it’s clear that SQL Server Agent was not running. So solution looked very simple, start the SQL Server Agent Service. It was not started by its own because it was set to manual mode.

Agent Not Running 03 SQL SERVER   FIX: SQLServerAgent is not currently running so it cannot be notified of this action. (Microsoft SQL Server, Error: 22022)

To avoid such error in the future, I change the “Start Mode” of the service, to automatic by going to the properties of the service.

Agent Not Running 04 SQL SERVER   FIX: SQLServerAgent is not currently running so it cannot be notified of this action. (Microsoft SQL Server, Error: 22022)

I also found that sometimes you would get this error even if the SQL Server Agent is running. In those cases, you need to check SQLAgent.out file to check if it’s started completely.

Something SQLAgent.out would show ONLY below message

Waiting for SQL Server to recover databases…

If you are running in that issue, please have a look at https://support.microsoft.com/en-us/kb/2640027 to get a fix for the issue.

Have you even fixed such simple issues? The error messages over the ages have become obvious and very helpful too.

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

SQL SERVER – Unable to drop login – Msg 15170, Level 16, State 1

Encountering error messages and solving them is something I often do. I think each and every error we encounter is validating our understanding in using the software and how we make simple mistakes.

As you might know, I do a lot of public speaking and many demos are done on my laptop. Due to which there are many unused objects in my SQL Server instance. To get my SQL instance to a clean state, I was trying to clean up the object and when I was trying to drop a login, I got below error:

DROP LOGIN [danny]
GO

job owner 01 SQL SERVER   Unable to drop login   Msg 15170, Level 16, State 1

Here is the error in text format:

Msg 15170, Level 16, State 1, Line 1

This login is the owner of 1 job(s). You must delete or reassign these jobs before the login can be dropped.

I first thought the error message is very self-explanatory to start with. The error message is clear that I need to modify the job which is owned by this login. Finding the job was easy for me as I had only few jobs. I was able to double click on each job and look at the owner in the UI.

job owner 02 SQL SERVER   Unable to drop login   Msg 15170, Level 16, State 1

On second thoughts, I felt – what if the server was some production database and had far too many jobs? What would a potential DBA do? So, if you have a server which has lots of job, opening properties of each job would not be a fun. Here is the script which can be used to find such jobs. You need to replace the login name with the appropriate name on your servers.

SELECT NAME
FROM msdb.dbo.sysjobs
WHERE owner_sid IN (
SELECT sid
FROM MASTER.sys.syslogins
WHERE NAME = 'danny'
)

job owner 03 SQL SERVER   Unable to drop login   Msg 15170, Level 16, State 1

To modify the owner either UI can be used or T-SQL. I have used below command to change the owner to sa.

USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name =N'foo', @owner_login_name=N'sa'
GO

After this I was able to drop the login. Have you ever been in this situation before? What did you do? Did you do something manually or wrote a similar script? Do let me know via comments.

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

SQL SERVER – Error and Fix for Msg 1907 Cannot recreate index The new index definition does not match the constraint being enforced by the existing index

This particular blog post is around usage of Clustered ColumnStore Indexes and how one of my customer in a recent conversation brought this up to my notice. They were on SQL Server 2012 and were upgrading to SQL Server 2014 and were very much interested in the usage of Clustered ColumnStore Index that was introduced with SQL Server. It made a lot of sense for them because now the data is updatable with CCI implementation.

I was with the dev team and I saw an interesting error message and since I was standing there, they just turned to me and asked – “Hey Pinal, I am getting an error in SQL Server and can you help me quickly.” Not expecting this coming my way, I told them with a warm smile – please goto my blog and search for the error message. I am sure there is already the solution that you are looking forward to. I thought I solved their problem and in less than 5 seconds the developer turned around and said – “Buddy, you don’t have any blog on it.” This got me curious because he is feeding an interesting conversation that I can use it in my blog. So I immediately sat down with him and the only activity I did was to read the error message and I was convinced with the solution in hand. The message read:

Msg 1907, Level 16, State 1, Line 5
Cannot recreate index ‘PK_FactResellerSales_SalesOrderNumber_SalesOrderLineNumber’.
The new index definition does not match the constraint being enforced by the existing index.

Let me walk you through the steps of achieving the same and what was the workaround. For this example, I am creating the table with a bunch of constraints.

CREATE DATABASE ColumnStoreDemos
GO
USE ColumnstoreDemos
GO
-- Create a normal table.
IF OBJECT_ID('FactResellerSales_Trim') IS NOT NULL
DROP TABLE FactResellerSales_Trim
GO
CREATE TABLE [dbo].FactResellerSales_Trim (
[ProductKey] [int] NOT NULL
,
[OrderDateKey] [int] NOT NULL
,
[DueDateKey] [int] NOT NULL
,
[ShipDateKey] [int] NOT NULL
,
[ResellerKey] [int] NOT NULL
,
[EmployeeKey] [int] NOT NULL
,
[PromotionKey] [int] NOT NULL
,
[CurrencyKey] [int] NOT NULL
,
[SalesTerritoryKey] [int] NOT NULL
,
[SalesOrderNumber] [nvarchar](20) NOT NULL
,
[SalesOrderLineNumber] [tinyint] NOT NULL
,
CONSTRAINT [PK_FactResellerSales_SalesOrderNumber_SalesOrderLineNumber] PRIMARY KEY CLUSTERED (
[SalesOrderNumber] ASC
,[SalesOrderLineNumber] ASC
)
)
ON [PRIMARY]
GO

You can add any data if required, but now assuming you have an existing table with data already there and you create an Clustered ColumnStore Index like below, you will get the same error:

-- Now Create a Columnstore Index to convert this table into Columnstore
CREATE CLUSTERED columnstore INDEX [PK_FactResellerSales_SalesOrderNumber_SalesOrderLineNumber] ON FactResellerSales_Trim
WITH (DROP_EXISTING = ON)
GO

Msg 1907, Level 16, State 1, Line 7
Cannot recreate index ‘PK_FactResellerSales_SalesOrderNumber_SalesOrderLineNumber’. The new index definition does not match the constraint being enforced by the existing index.

The error above indicates that we have an unsupported constraint. Primary keys or foreign key constraints are not supported on a table with clustered columnstore. Given that, if we want to create a clustered columnstore, we have to drop the constraints. Are we able to create the clustered columnstore index?

ALTER TABLE FactResellerSales_Trim
DROP CONSTRAINT [PK_FactResellerSales_SalesOrderNumber_SalesOrderLineNumber]
GO
CREATE CLUSTERED COLUMNSTORE INDEX [CCI_FactResellerSales] ON FactResellerSales_Trim
GO

We can look at the successful creation of our Clustered ColumnStore Index using the query to DMVs:

SELECT * FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('FactResellerSales_Trim')

Though this is a simple and self-explanatory error message. I thought I have not covered the same in this blog and just making sure that next time I tell someone to search – it shows up to help them. Do let me know if you ever get this error while you were migrating to the new version of SQL Server.

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

SQL SERVER – Unable to Start SQL Server After Patching

This is one of the common question which lands in my mailbox via email.

Hi Pinal,
I have an issue on my UAT environment. I was unable to start the SQL server 2008 R2 instance after applying SP1. We see the following error logged in the SQL Application log

Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 598, state 1, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

Can you please help?

I have asked to share SQL Server ERRORLOG and here is what we found.

2015-12-10 12:20:20.20 spid7s      Error: 5123, Severity: 16, State: 1.
2015-12-10 12:20:20.20 spid7s      CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\temp_MS_AgentSigningCertificate_database.mdf’.
2015-12-10 12:20:20.20 spid7s      Error: 5123, Severity: 16, State: 1.
2015-12-10 12:20:20.20 spid7s      CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\temp_MS_AgentSigningCertificate_database.mdf’.
2015-12-10 12:20:20.20 spid7s      Error: 1802, Severity: 16, State: 4.
2015-12-10 12:20:20.20 spid7s      CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
2015-12-10 12:20:20.20 spid7s      Error: 912, Severity: 21, State: 2.
2015-12-10 12:20:20.20 spid7s      Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 598, state 1, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

If you don’t know where ERRORLOG is, then refer below blog.

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

What is “Script upgrade mode”?

Based on my understanding, when we apply any patch on SQL Server instance, the patching, upgrades the system databases (views, objects) immediately after the restart of the SQL Server Services. The stopping of the services and starting it again is a part of the patching process.  There are certain scripts (most of the times sqlagentxxx_msdb_upgrade.sql) in the patch/SP which is applied only after the SQL Server service restarts. During the Process of applying these scripts if anyone tries to connect to SQL Server instance, then he/she will get the below error message.

Login failed for user ‘LoginName’ :Reason: Server is in script upgrade mode. Only administrator can connect at this time.

I have also explained it in an earlier blog

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

So, here is our problem in this case.

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\temp_MS_AgentSigningCertificate_database.mdf’.

I asked him to check the service account for SQL Server and it was set to the Network Service. When we checked permission on the folder C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSQLSERVER\MSSQL It did not have permissions for the Network Service account. I suggested to change this to Local System account OR provide permission, so that the SQLServer.exe process account (service account) will have permission and will be able to create the temp database and let the script upgrade complete.

Once permission was given the restart was successful.

Have you ever encountered any such issue of upgrade script mode?

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

SQL SERVER – Could Not Register Service Principal Name Persists – Notes from the Field #105

ryanadams SQL SERVER   Could Not Register Service Principal Name Persists   Notes from the Field #105[Note from Pinal]: In this episode of the Notes from the Field series database expert Ryan Adams explains a very critical error user receive when registering service principals. Ryan is one guy who spends more time with real world issues with SQL Server than anything else. He has mastered the art of resolving complex errors and document them so easily that one can’t find anywhere else. In this blog post Ryan addresses a very interesting error related to Service Principal Name. Read the experience of  Ryan in her own words.


Have you ever seen the error below in your SQL Server log shortly after startup?  You’ll actually see two of them and you can see the difference between them in the screen shot, but here is the text.

The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/Node1.stars.com:1433 ] for the SQL Server Service.  Windows return code: 0x2098, state: 15.  Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos.  This is an informational message.  Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

Kerb Error in Log SQL SERVER   Could Not Register Service Principal Name Persists   Notes from the Field #105

So what causes this error and how can you fix it?  The first thing to note is that it is an informational message and not actually an error.  There are two scenarios in which you will see this message.

  1. The first scenario is what you see right out of the box.  SQL Server does not have the rights on a domain service account to register Service Principal Names (SPNs).  The description we see here is very clear in telling us that SQL Server could not register the required SPNs.  It even tells us the exact SPN syntax it attempted to register.  If you want to use Kerberos you have to register the SPNs manually or give the service account the right to perform the change itself.  If you decided to register them manually, then now is a good time to write down the SPNs from the description.
  2. The second scenario is a weird one that throws people off.  If you choose to manually register the SPNs on the service account and restart SQL Server, you’ll still see the same message in the log.  Now why in the world would this message even show up if you already registered the SPNs?  In fact, many folks will see this message and assume they are not using Kerberos, because the message clearly states that it could not register the SPNs.  The assumption is usually that they got the SPN syntax wrong or that the SPNs never got registered.

Just for kicks, let’s jump back over to my test server and take a look at the current connections.  Most folks will add a WHERE clause to the following query to just look at their current connection, but I’m going to caution you about that.  If you’re on the server itself, you won’t get accurate results because you end up using Named Pipes unless it’s disabled.  We are looking to see if there are any Kerberos connections at all so we don’t want to filter the result set.

SELECT * FROM sys.dm_exec_connections
--WHERE session_id = @@SPID

Kerb Connection SQL SERVER   Could Not Register Service Principal Name Persists   Notes from the Field #105

Well that’s an interesting result, huh?  I clearly have Kerberos connections despite the message I keep getting in the SQL Server log.  So why is that?  Well it comes down to the semantics of the message.  The message said it couldn’t register the SPNs and that’s true.  It couldn’t register them because you already did it.  So if you ever see this message, make sure you go look at your connections first (using the above query) to see if it is something you need to address or not.  If you see KERBEROS in the auth_scheme column, then you are all set.

If you want the message to go away completely, there is only one way to do that.  You have to give the account running the SQL Server service the permissions to change its own SPNs.  You can do that by opening the properties of the account and heading to the security tab.  You will find an account in the account list called SELF.  Grant that account “Write to Public Information”, restart the SQL Server service, and the message will disappear.  Now you’ll see a new message stating that SQL Server was able to successfully register the required SPNs.

If you are looking for more information around Kerberos and SQL Server you can visit my blog for more articles.  This is one of the many checks we perform during our WellDBA exams here at Linchpin People.  If you’re interested in taking us a look at your systems and perform a WellDBA exam, we would be happy to work with you.  You can find more information here about the Linchpin WellDBA exams.

If you want to get started with SQL Server with the help of experts, read more over at Fix Your SQL Server.

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

SQL SERVER – InMemory Table Creation Error Message: Msg 41337

I had recently gone to my hometown to visit my parents as it was holidays for my daughter because of festival. These are relaxing times and hardly try to get in front of my computer during these 2-3 days of family time. But who said that happens always. In this recent trip, I had one of my cousins visit our place who was incidentally studying his engineering degree. We suddenly got into a conversation and I started talking about InMemory databases and how they can be made resilient. Though this topic becomes a great conversation starter to college students, he was baffled with the fact that we can have InMemory databases that are ACID compliant.

He got to his home and said he would play around with this concept because he had a SQL Server 2016 evaluation version. I said there are many more enhancements to talk but wanted him to do his bit of search and homework before I taught him some really cool tricks. That night he sent me a mail stating he was getting some error while creating an InMemory table and was not sure what to do. I asked him to send me the error to take a first look:

Msg 41337, Level 16, State 100, Line 2
Cannot create memory optimized tables. To create memory optimized tables, the database must have a MEMORY_OPTIMIZED_FILEGROUP that is online and has at least one container.

It was clear from the message the source of the problem. He had created a normal database without the InMemory filegroup.

I sent him the script to get rid of the error message and is shown below:

-- Create the Database
-- Note the filegroup of type MEMORY_OPTIMIZED_DATA
USE MASTER
GO
IF DB_ID('InMem_OLTP') IS NOT NULL
BEGIN
ALTER DATABASE
[InMem_OLTP] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE
[InMem_OLTP]
END
CREATE DATABASE
[InMem_OLTP]
ON  PRIMARY
( NAME = N'InMem_OLTP_data', FILENAME = N'C:\DATA\InMem_OLTP_data.mdf',size = 50MB)
LOG ON
( NAME = N'InMem_OLTP_log', FILENAME = N'C:\DATA\InMem_OLTP_log.ldf',size = 10MB)
GO
--- Step 2 - Can we create this table?
USE InMem_OLTP
GO
CREATE TABLE [Customer](
[CustomerID] INT NOT NULL
PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 10000),
)
WITH (MEMORY_OPTIMIZED = ON,  DURABILITY = SCHEMA_AND_DATA  );
GO

As you can see we will get the exact error as mentioned earlier.

Msg 41337, Level 16, State 100, Line 2
Cannot create memory optimized tables. To create memory optimized tables, the database must have a MEMORY_OPTIMIZED_FILEGROUP that is online and has at least one container.

As mentioned, let us add the InMemory Filegroup so that we can create the tables without a glitch:

-- Step 3 -- Add a Filegroup that is MEMORY_OPTIMIZED.
USE InMem_OLTP
GO
ALTER DATABASE InMem_OLTP
ADD Filegroup [InMem_OLTP_InMemory] CONTAINS MEMORY_OPTIMIZED_DATA
GO
ALTER DATABASE InMem_OLTP
ADD FILE (NAME = InMem_OLTP_InMemory, FILENAME = N'C:\Data\InMem_OLTP_mopt')
TO FILEGROUP [InMem_OLTP_InMemory]

Now if we create the table as mentioned in Step 2 above, there will not be any error. I don’t blame my cousin for this because he was a novice and was not able to understand what a container is when it comes to InMemory tables. Though this was a simple error that most of you would easily be able to solve, it can surely be a great source troubleshooting for people like my cousin.

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

SQL SERVER – Installation failed with error – Wait on the Database Engine recovery handle failed

You might have seen on Facebook that I have completed the new setup of my machines. Since I use VMs to do my tests. To have a domain, I made a VM as domain controller and another as a member server. Next step was to install SQL Server and by mistake, I have run the setup of SQL on a domain controller. As per Microsoft documentation they don’t recommend installing SQL on the domain controller and there is a warning in SQL Setup.

setup dc 01 SQL SERVER   Installation failed with error   Wait on the Database Engine recovery handle failed

—————————
Rule Check Result
—————————
Rule “Computer domain controller” generated a warning.
Installing SQL Server 2016 CTP2.4 on a domain controller is not recommended.
—————————
OK
—————————
I proceeded further and it failed at the end with below error
Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.
Here is the complete message in summary.txt file.
Feature:                       SQL Server Replication
Status:                        Failed: see logs for details
Reason for failure:            An error occurred for a dependency of the feature causing the setup process for the feature to fail.
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.
I followed the error message as it asked to check ERRORLOG. Here is message in ERRORLOG
Error: 17190, Severity: 16, State: 1.
Initializing the FallBack certificate failed with error code: 1, state: 20, error number: 0.
Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.
Error: 17182, Severity: 16, State: 1.
TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.
Error: 17182, Severity: 16, State: 1.
TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property.
Error: 17826, Severity: 18, State: 3.
Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
Error: 17120, Severity: 16, State: 1.
SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

I searched on the internet and found that this could be issued with a SQL Service account. I selected all default configuration and it was NT Service\MSSQLServer

I uninstalled SQL Server and this time I selected Local System account for SQL Service in setup wizard and voilà it went fine.

Have you ever faced any such error on a regular machine? What was the solution?

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