In-Memory Table Alter fails

Question

In-Memory Table Alter fails

gkolson

Old Hand

Points: 331
More actions
April 19, 2017 at 9:33 am

#401967

Created an In-Memory demo db to play with in SQL Server 2016 SP1, and I am getting a failure each time I try to alter the table. I set it up based off the MS demos in:https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/altering-memory-optimized-tables Error I receive: Msg 41317, Level 16, State 3, Procedure SWITCHERS, Line 9 [Batch Start Line 57]A user transaction that accesses memory optimized tables or natively compiled modules cannot access more than one user database or databases model and msdb, and it cannot write to master. Anyone ever run into this, and how do you work around it?
Alter database [InMemoryTables] ADD FILE (NAME='InMem', FILENAME='D:\DBData\InMem') TO FILEGROUP InMemory
Alter database [InMemoryTables] set MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT ON
CREATE TABLE dbo.tickets ( [TicketID] int not null PRIMARY KEY NONCLUSTERED,
[Name] varchar(32) not null , [City] varchar(32) null, [State_Cde] varchar(2) null,
[Ticket_Status] varchar(10) null, [LastModified] datetime not null, ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
Alter TABLE dbo.tickets add [Ticket_Category] int not null DEFAULT 1 WITH VALUES
Thanks!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398715 More actions · Answer 1

I'm not sure why you're getting the error. I modified your script just slightly (mainly adding the CREATE DATABASE and USE DATABASE commands) and it ran just fine:

CREATE DATABASE InMemoryTables;

ALTER DATABASE InMemoryTablesADD FILEGROUP InMemoryCONTAINS MEMORY_OPTIMIZED_DATAGOALTER DATABASE InMemoryTables ADD FILEGROUP InMemory CONTAINS MEMORY_OPTIMIZED_DATA GO

ALTER DATABASE InMemoryTablesADD FILE(NAME = 'InMem', FILENAME = 'c:\Data\InMem' )TO FILEGROUP InMemoryALTER database InMemoryTables SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT ONALTER DATABASE InMemoryTables ADD FILE(NAME = 'InMem', FILENAME = 'c:\Data\InMem' ) TO FILEGROUP InMemory ALTER database InMemoryTables SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT ON

USE InMemoryTables;USE InMemoryTables;

CREATE TABLE dbo.tickets ( TicketID INT NOT NULL PRIMARY KEY NONCLUSTERED, Name VARCHAR(32) NOT NULL, City VARCHAR(32) NULL, State_Cde VARCHAR(2) NULL, Ticket_Status VARCHAR(10) NULL, LastModified DATETIME NOT NULL, )WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);CREATE TABLE dbo.tickets ( TicketID INT NOT NULL PRIMARY KEY NONCLUSTERED, Name VARCHAR(32) NOT NULL, City VARCHAR(32) NULL, State_Cde VARCHAR(2) NULL, Ticket_Status VARCHAR(10) NULL, LastModified DATETIME NOT NULL, ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

ALTER TABLE dbo.tickets
ADD Ticket_Category INT NOT NULL
       DEFAULT 1 WITH VALUES;

Be sure you have the latest Cumulative Update & Service Pack installed.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

gkolson Old Hand Points: 331 More actions · Answer 2

What version are you running? I'm on Microsoft SQL Server 2016 (SP1-CU2) (KB4013106) - 13.0.4422.0 (X64) Mar 6 2017 14:18:16 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor) and still receiving the error.

Grant Fritchey SSC Guru Points: 398715 More actions · Answer 3

Actually, you're a little ahead of me. I need to download the latest CU. I'm here: Microsoft SQL Server 2016 (SP1-CU1) (KB3208177) - 13.0.4411.0 (X64) Jan 6 2017 14:24:37 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

I'll download the CU & retest.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Grant Fritchey SSC Guru Points: 398715 More actions · Answer 4

OK. Redid it all and I'm still not getting the error. The one thing that might be causing it is putting a USE database statement in the sample code prior to installing tables, etc. My script above looks like crap for some reason. Here's a repaste

CREATE DATABASE InMemoryTables;

ALTER DATABASE InMemoryTables
ADD FILEGROUP InMemory
CONTAINS MEMORY_OPTIMIZED_DATA
GO

ALTER DATABASE InMemoryTables ADD FILEGROUP InMemory CONTAINS MEMORY_OPTIMIZED_DATA GO

ALTER DATABASE InMemoryTables
ADD FILE(NAME = 'InMem',         FILENAME = 'c:\Data\InMem'        )TO FILEGROUP InMemory
ALTER database InMemoryTables SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT ON

ALTER DATABASE InMemoryTables ADD FILE(NAME = 'InMem', FILENAME = 'c:\Data\InMem' ) TO FILEGROUP InMemory ALTER database InMemoryTables SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT ON USE InMemoryTables;

CREATE TABLE dbo.tickets (  TicketID INT NOT NULL PRIMARY KEY NONCLUSTERED,                            
Name VARCHAR(32) NOT NULL,                            
City VARCHAR(32) NULL,                            
State_Cde VARCHAR(2) NULL,                           
 Ticket_Status VARCHAR(10) NULL,                           
 LastModified DATETIME NOT NULL,                         )
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

CREATE TABLE dbo.tickets ( TicketID INT NOT NULL PRIMARY KEY NONCLUSTERED, Name VARCHAR(32) NOT NULL, City VARCHAR(32) NULL, State_Cde VARCHAR(2) NULL, Ticket_Status VARCHAR(10) NULL, LastModified DATETIME NOT NULL, ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

ALTER TABLE dbo.ticketsADD Ticket_Category INT NOT NULL DEFAULT 1 WITH VALUES;

ALTER TABLE dbo.tickets ADD Ticket_Category INT NOT NULL DEFAULT 1 WITH VALUES;

That looks better.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 5

Grant Fritchey - Monday, April 24, 2017 12:54 PM
OK. Redid it all and I'm still not getting the error. The one thing that might be causing it is putting a USE database statement in the sample code prior to installing tables, etc. My script above looks like crap for some reason. Here's a repaste
<snip>
That looks better.

Up to a point, guv. The point being where you realise that each statement in your query appears to be a duplicate (albeit differently-formatted) duplicate of the preceding statement...

...which doesn't help the OP with their problem. Sorry. I'll go crawl back under my rock until I've had more coffee.

Thomas Rushton
blog: https://thelonedba.wordpress.com

Grant Fritchey SSC Guru Points: 398715 More actions · Answer 6

ThomasRushton - Tuesday, April 25, 2017 4:04 AM
Grant Fritchey - Monday, April 24, 2017 12:54 PM
OK. Redid it all and I'm still not getting the error. The one thing that might be causing it is putting a USE database statement in the sample code prior to installing tables, etc. My script above looks like crap for some reason. Here's a repaste
<snip>
That looks better.
Up to a point, guv. The point being where you realise that each statement in your query appears to be a duplicate (albeit differently-formatted) duplicate of the preceding statement...
...which doesn't help the OP with their problem. Sorry. I'll go crawl back under my rock until I've had more coffee.

That's so weird. I've re-edited that multiple times. Not sure what the heck is going on.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Grant Fritchey SSC Guru Points: 398715 More actions · Answer 7

Tried editing again. There are no duplicate lines in the original text it's something in the formatter.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

gkolson Old Hand Points: 331 More actions · Answer 8

FYI update: We are going to open a case with Microsoft for the issue, see attached profiler screenshot. The conspiracy theory at the moment is the alter is somehow doing a database switch as notice for the Exception the db changed to msdb. We don't on our server installs set a ton of custom configurations on our servers, but either way with what we have now we would like In Memory to be able to work properly.

Grant Fritchey SSC Guru Points: 398715 More actions · Answer 9

Sorry I couldn't help. I just can't replicate the issue. Going to Microsoft is probably the best guess right now.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Saurabh.D Ten Centuries Points: 1104 More actions · Answer 10

Refer below link

https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/unsupported-sql-server-features-for-in-memory-oltp

Transactional DDL. CREATE/ALTER/DROP of In-Memory OLTP objects is not supported inside user transactions.

ThanksSaurabh.D