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:

1^st 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

• Anonymous
  January 13, 2017
  Separating out the steps didn't help us with the ...failed because there are open records with start of period set to a value in the future error. This was caused by using GETUTCDATE() as the default for sysstarttime. Because of our geographic location this created a time later in the day than the current time which, when evaluated by SQL Server caused the error. We solved this by changing the default for sysstarttime to GetDate() which is always in the past. Please note that this only happens if there are existing records in the base table that a temporal table is being created from. No records, no problem. However, in our case records did exist and putting a future time in systarttime cause the script to fail. Hope this might help someone else.
• Anonymous
  July 07, 2017
  @Craig We having the same scenario with existing records in the base table. Our geographic location has a local time later in the day than the current UTC time. Tried by changing the default for sysstarttime to GetDate() and not fixing the issue.Any help?