July, 2018 | Ned Otter Blog

I recently saw a thread on twitter, where the OP talked about setting the max size for an In-Memory OLTP container. I responded as I always do: it’s not possible to set a limit on anything having to do with storage for In-Memory OLTP.

Unfortunately, that’s not correct: through SSMS or TSQL, you can in fact set a max size for a container.

But you should not ever do that…..

Why?

Because if you do, and your checkpoint files exceed the max size of the container, your database can go into the In Recovery, Suspect, or OFFLINE state. The following code reproduces this issue:

USE master
GO

DROP DATABASE IF EXISTS InMemTest

CREATE DATABASE InMemTest

EXEC sp_helpdb InMemTest

USE InMemTest
GO

ALTER DATABASE InMemTest ADD FILEGROUP InMemTestFG CONTAINS MEMORY_OPTIMIZED_DATA

ALTER DATABASE InMemTest ADD FILE
(
     NAME = 'Container1'
    ,FILENAME = 'H:\SQLDATA\InMemTest_Container1'
)
TO FILEGROUP InMemTestFG

/*
#########################
    sp_helpdb doesn't show the size 
    of the containers for a memory-optimized database,
    so we must reference sys.dm_db_xtp_checkpoint_files

#########################
*/

DROP TABLE IF EXISTS dbo.InMemT1

CREATE TABLE dbo.InMemT1
(
     PKcol INT IDENTITY PRIMARY KEY NONCLUSTERED
    ,description VARCHAR(8000) NOT NULL
)
WITH (DURABILITY = SCHEMA_AND_DATA, MEMORY_OPTIMIZED = ON)

-- verify how much space the checkpoint files consume. On my system, it/s 936MB, 
-- so I set the max container size to 1000MB 

SELECT FORMAT(SUM(file_size_in_bytes / 1048576.0), '####') AS fileSizeMBTotal
FROM sys.dm_db_xtp_checkpoint_files
GO

ALTER DATABASE InMemTest MODIFY FILE
(
     NAME = 'Container1'
    ,MAXSIZE = 1000MB
)

USE InMemTest
GO
BACKUP DATABASE InMemTest TO DISK = 'nul' WITH STATS = 1
BACKUP LOG InMemTest TO DISK = 'nul' WITH STATS = 1

SELECT FORMAT(SUM(file_size_in_bytes / 1048576.0), '####') AS fileSizeMBTotal
FROM sys.dm_db_xtp_checkpoint_files


SET NOCOUNT ON 
INSERT InMemT1
(
    description
)
SELECT REPLICATE('A', 100)
GO 1000

-- we're good up to here, but issuing this CHECKPOINT

CHECKPOINT -- this CHECKPOINT succeeds

-- now I see 952MB 
SELECT FORMAT(SUM(file_size_in_bytes / 1048576.0), '####') AS fileSizeMBTotal
FROM sys.dm_db_xtp_checkpoint_files

-- you might have to do this a few times, before the subsequent CHECKPOINT will fail
BACKUP LOG InMemTest TO DISK = 'nul' WITH STATS = 1

/*
###################
    running this checkpoint causes the files in the container to grow beyond 1000MB
###################
*/
CHECKPOINT

/*
    Msg 9001, Level 21, State 4, Line 88
    The log for database 'InMemTest' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    Msg 596, Level 21, State 1, Line 87
    Cannot continue the execution because the session is in the kill state.
    Msg 0, Level 20, State 0, Line 87
    A severe error occurred on the current command.  The results, if any, should be discarded.

    database goes to "In Recovery" state

    restart SQL Server service
    database goes to "Suspect" state
    after a while, the db is OFFLINE

*/

USE master
GO
ALTER DATABASE InMemTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE InMemTest SET OFFLINE

-- db goes to In Recovery, Recovery Pending, and then finally OFFLINE state
ALTER DATABASE InMemTest SET ONLINE


-- fails
ALTER DATABASE InMemTest MODIFY FILE
(
     NAME = 'Container1'
    ,MAXSIZE = 2000MB
)

SELECT FORMAT(SUM(file_size_in_bytes / 1048576.0), '####') AS fileSizeMBTotal
      ,FORMAT(SUM(file_size_used_in_bytes / 1048576.0), '####') AS fileSizeMBUsed
      ,SUM(file_size_used_in_bytes / 1048576.0)
FROM sys.dm_db_xtp_checkpoint_files

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

USE master

DROP DATABASE IF EXISTS InMemTest

CREATE DATABASE InMemTest

EXEC sp_helpdb InMemTest

USE InMemTest

ALTER DATABASE InMemTest ADD FILEGROUP InMemTestFG CONTAINS MEMORY_OPTIMIZED_DATA

ALTER DATABASE InMemTest ADD FILE

(

NAME = 'Container1'

,FILENAME = 'H:\SQLDATA\InMemTest_Container1'

)

TO FILEGROUP InMemTestFG

#########################

sp_helpdb doesn't show the size

of the containers for a memory-optimized database,

so we must reference sys.dm_db_xtp_checkpoint_files

#########################

DROP TABLE IF EXISTS dbo.InMemT1

CREATE TABLE dbo.InMemT1

(

PKcol INT IDENTITY PRIMARY KEY NONCLUSTERED

,description VARCHAR(8000) NOT NULL

)

WITH (DURABILITY = SCHEMA_AND_DATA, MEMORY_OPTIMIZED = ON)

-- verify how much space the checkpoint files consume. On my system, it/s 936MB,

-- so I set the max container size to 1000MB

SELECT FORMAT(SUM(file_size_in_bytes / 1048576.0), '####') AS fileSizeMBTotal

FROM sys.dm_db_xtp_checkpoint_files

ALTER DATABASE InMemTest MODIFY FILE

(

NAME = 'Container1'

,MAXSIZE = 1000MB

)

USE InMemTest

BACKUP DATABASE InMemTest TO DISK = 'nul' WITH STATS = 1

BACKUP LOG InMemTest TO DISK = 'nul' WITH STATS = 1

SELECT FORMAT(SUM(file_size_in_bytes / 1048576.0), '####') AS fileSizeMBTotal

FROM sys.dm_db_xtp_checkpoint_files

SET NOCOUNT ON

INSERT InMemT1

(

description

)

SELECT REPLICATE('A', 100)

GO 1000

-- we're good up to here, but issuing this CHECKPOINT

CHECKPOINT -- this CHECKPOINT succeeds

-- now I see 952MB

SELECT FORMAT(SUM(file_size_in_bytes / 1048576.0), '####') AS fileSizeMBTotal

FROM sys.dm_db_xtp_checkpoint_files

-- you might have to do this a few times, before the subsequent CHECKPOINT will fail

BACKUP LOG InMemTest TO DISK = 'nul' WITH STATS = 1

###################

running this checkpoint causes the files in the container to grow beyond 1000MB

###################

CHECKPOINT

Msg 9001, Level 21, State 4, Line 88

The log for database 'InMemTest' is not available. Check the event log for related error messages. Resolve any errors and restart the database.

Msg 596, Level 21, State 1, Line 87

Cannot continue the execution because the session is in the kill state.

Msg 0, Level 20, State 0, Line 87

A severe error occurred on the current command. The results, if any, should be discarded.

database goes to "In Recovery" state

restart SQL Server service

database goes to "Suspect" state

after a while, the db is OFFLINE

USE master

ALTER DATABASE InMemTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE InMemTest SET OFFLINE

-- db goes to In Recovery, Recovery Pending, and then finally OFFLINE state

ALTER DATABASE InMemTest SET ONLINE

-- fails

ALTER DATABASE InMemTest MODIFY FILE

(

NAME = 'Container1'

,MAXSIZE = 2000MB

)

SELECT FORMAT(SUM(file_size_in_bytes / 1048576.0), '####') AS fileSizeMBTotal

,FORMAT(SUM(file_size_used_in_bytes / 1048576.0), '####') AS fileSizeMBUsed

,SUM(file_size_used_in_bytes / 1048576.0)

FROM sys.dm_db_xtp_checkpoint_files

Note that I’ve not yet found a way around this. The OP from that thread on twitter said he had to actually restart the SQL Server service to resolve the issue with that database, but I don’t see why that would make any difference (when I tried it, the database attempted recovery, but eventually went offline).

Setting a max size for the container is a really, really really bad idea, because it guarantees that the database will have some form of outage when you hit the threshold. The bottom line is that containers must be free to grow, period. That’s part of the capacity planning good DBAs will do before deploying the In-Memory OLTP feature.

Decades ago, before Microsoft SQL Server existed, I spent $500 (quite a hefty sum in those days) attempting to learn C language programming and Unix. It was the best $500 I ever spent, because it informed me that my brain simply does not work well with that technology (or at least, it didn’t back then). Fast forward to 2017, and voila!: SQL Server runs on Linux. But this time, there are some big differences. For one thing, Powershell can ease the burden of learning *nix commands. Also, Linux has the ability to install a desktop.

And so I’ve begun my deep dive into various aspects of running SQL Server on Linux, and Ubuntu was my distribution of choice.

Windows man

This life-long Windows SQL Server DBA depends on the ease with which one can copy and paste in either direction between a guest VM and the host, using RDP – it’s a huge time saver. Folks in the Linux world love to type stuff, and that’s ok with me, because I started in technology in the days before Windows existed, so I’m a seasoned MS-DOS/command line guy.

While researching various aspects of what’s possible on Linux, I read a lot of blog posts, and some of them had long lists of commands. While I could have collected those commands into a file on my Windows host and copied that file to the Linux guest, I simply wanted to copy and paste to and from my Ubuntu VM running on Hyper-V.

Alas, that was not to be.

If you search the web for “copy paste Ubuntu Hyper-V”, you’ll find loads of answers in forums, dispensing all types of advice that might have been good at that time. But now it’s 2018, and I tripped across this blog post from Craig Wilhite @ Microsoft:

Sneak Peek: Taking a Spin with Enhanced Linux VMs

There, it details how to go about setting up Enhanced Linux VMs, and so I downloaded Ubuntu Server 18.04, and got to work, following that blog post to the letter.

Denied

I spent the better part of a week after hours, trying to get this to work, plugging the error messages into search engines to see what came back.

After entering credentials into xrdp, I received the message: “Video remoting was disconnected”, and searching for that led me to this thread on github, which is related to Craig Wilhite’s post.

So clearly, others had experienced this issue, but there didn’t seem to be any resolution. I posted a message, asking for what next steps I might take, and followed recommendations, but nothing panned out. Finally, Craig suggested that perhaps the difference was due to the fact that I was using Ubuntu server, and he had verified the steps using Ubuntu desktop. I just finished testing with Ubuntu desktop, and hallelujah, Enhanced Session Linux VMs work with Ubuntu desktop.

But the entire reason I wanted to experiment with the server version was to investigate Kubernetes, and I wanted to use Ubuntu server for that.

As luck would have it, the next day I attended a webinar given by Argenis Fernandez (b| t) on using SQL Server on containers, and during the presentation, Argenis mentioned MobaXterm, which allows copy/paste, and has a free version. So I reinstalled Ubuntu server, installed MobaXterm, and lo and behold, I now have bidirectional copy/paste between host and guest.

That’s how it is when you learn any new, unfamiliar technology – you spin your wheels, make mistakes, fail, and if you push through and leave your mind open, you can be rewarded with expertise.

Ned Otter Blog

SQL Server DBA and Musician

Monthly Archives: July 2018

Dangerous moves: Setting max size for In-Memory OLTP containers

Trials and tribulations of learning Linux