Msg 13575 OR Msg 13542 error message while adding period columns

You may see following error message while trying to enable system-versioned temporal table and adding period columns for a table which contain data:

1st error message:

Msg 13575, Level 16, State 0, Line 32

ADD PERIOD FOR SYSTEM_TIME failed because table ‘temporaltest.dbo.CUSTOMERINFO’ contains records where end of period is not equal to MAX datetime.

2nd error message:

Msg 13542, Level 16, State 0, Line 8733

ADD PERIOD FOR SYSTEM_TIME on table ‘ temporaltest.dbo.CUSTOMERINFO’ failed because there are open records with start of period set to a value in the future.

This issue can occur for memory optimized table as well as non-memory optimized table.

You can reproduce this issue using following T-SQL:

/************************************************************************************************************************/

CREATE DATABASE [temporaltest]

CONTAINMENT = NONE

ON PRIMARY

( NAME = N’temporaltest’, FILENAME = N’E:\temp\temporaltest.mdf’ , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ),

FILEGROUP [imoltp_mod] CONTAINS MEMORY_OPTIMIZED_DATA DEFAULT

( NAME = N’imoltp_mod1′, FILENAME = N’e:\temp\imoltp_mod1′ , MAXSIZE = UNLIMITED)

LOG ON

( NAME = N’temporaltest_log’, FILENAME = N’E:\temp\temporaltest_log.ldf’ , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )

GO

use temporaltest

GO

CREATE TABLE [dbo].[CUSTOMERINFO]

(

[ID] [bigint] IDENTITY(1,1) NOT NULL,

[EMPID] [int] NOT NULL,

CONSTRAINT [CUSTOMERINFO_primaryKey] PRIMARY KEY NONCLUSTERED HASH

(

[EMPID]

)WITH ( BUCKET_COUNT = 128)

)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )

GO

insert into [CUSTOMERINFO] ([EMPID]) select 1

GO

select * from [CUSTOMERINFO]

GO

ALTER TABLE [CUSTOMERINFO]

ADD PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime),

SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL DEFAULT GETUTCDATE(),

SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL DEFAULT CONVERT(DATETIME2, ‘9999-12-31 23:59:59.99999999’);

/************************************************************************************************************************/

WORKAROUND

If you are seeing same symptoms in that case you can break your T-SQL in 4 parts as shown below which should help you to resolve this issue:

/************************************************************************************************************************/

ALTER TABLE [CUSTOMERINFO]

ADD

SysStartTime datetime2 NOT NULL DEFAULT GETUTCDATE(),

SysEndTime datetime2 NOT NULL DEFAULT CONVERT(DATETIME2, ‘9999-12-31 23:59:59.99999999’);

GO

ALTER TABLE [CUSTOMERINFO]

ADD PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);

GO

ALTER TABLE [CUSTOMERINFO]

alter column SysStartTime ADD HIDDEN;

GO

ALTER TABLE [CUSTOMERINFO]

alter column SysEndTime ADD HIDDEN;

/************************************************************************************************************************/

 

Vikas Rana (@vikasrana_dba)

Support Escalation Engineer
Microsoft India GTSC