Example of SQL Server 2014 RTM log growth problem

USE [master];
GO

--- New database, 100 MB rows and 100 MB log file, fixed growth and max size.
CREATE DATABASE LogUseTest ON
    PRIMARY (FILENAME=N'X:\LogUseTest.mdf', NAME=N'LogUseTest', SIZE=102400KB, MAXSIZE=UNLIMITED, FILEGROWTH=102400KB),
    FILEGROUP [MO] CONTAINS MEMORY_OPTIMIZED_DATA
        DEFAULT (FILENAME='X:\LogUseTest_MO', NAME=N'LogUseTest_MO', MAXSIZE=UNLIMITED)
       LOG ON (FILENAME='X:\LogUseTest.ldf', NAME=N'LogUseTest_log', SIZE=102400KB, MAXSIZE=102400KB, FILEGROWTH=102400KB);
GO

--- SQL Server 2014, simple mode, read-write, no funny business.
ALTER DATABASE LogUseTest SET COMPATIBILITY_LEVEL=120;
ALTER DATABASE LogUseTest SET RECOVERY SIMPLE;
ALTER DATABASE LogUseTest SET PAGE_VERIFY CHECKSUM;
ALTER DATABASE LogUseTest SET FILESTREAM (NON_TRANSACTED_ACCESS=OFF);
ALTER DATABASE LogUseTest SET TARGET_RECOVERY_TIME=0 SECONDS;
ALTER DATABASE LogUseTest SET DELAYED_DURABILITY=DISABLED;
ALTER DATABASE LogUseTest SET READ_WRITE;
GO

USE LogUseTest;
GO

CREATE TABLE dbo.table1 (
    _id        int IDENTITY(1, 1) NOT NULL,
    filler     char(7900) NOT NULL,
    CONSTRAINT PK_table1 PRIMARY KEY CLUSTERED (_id)
);

--- Add some sample data to almost fill up the database:
INSERT INTO dbo.table1 (filler) SELECT REPLICATE('A', 7900);
WHILE (@@ROWCOUNT<=2048)
    INSERT INTO dbo.table1 (filler) SELECT filler FROM dbo.table1;

--- Returns XTP_CHECKPOINT:
SELECT log_reuse_wait_desc FROM sys.databases WHERE database_id=DB_ID();

--- Data file is 66 MB, log file is 72 MB
SELECT [name], type_desc, FILEPROPERTY([name], 'SpaceUsed')*8/1024 AS used_size_mb
FROM sys.database_files;





--- This INSERT will fill the log file to capacity, returning error msg 9002
INSERT INTO dbo.table1 (filler) SELECT filler FROM dbo.table1;

--- If the log file had been cleared by now, the INSERT would complete without errors.

--- After the Msg 9002, if you try to..
CHECKPOINT;
--- ... you're awarded with:
--- Msg 5901, Level 16, State 1, Line 46
--- One or more recovery units belonging to database 'LogUseTest' failed to generate a checkpoint.
--- This is typically caused by lack of system resources such as disk or memory, or in some cases
--- due to database corruption. Examine previous entries in the error log for more detailed information
--- on this failure.

--- .. and:

--- Msg 9002, Level 17, State 1, Line 46
--- The transaction log for database 'LogUseTest' is full due to 'CHECKPOINT'.


--- Just issuing the CHECKPOINT statement without exploding the log file does nothing to the database.
--- After some 10-15 minutes, I noticed that the log file was down to 10 MB.
--- The XTP_CHECKPOINT wait remains, though.
GO




---------------------------------------
--- Clean-up.
GO
USE master;
GO
DROP DATABASE LogUseTest;
GO