Sql-server – ADD PERIOD FOR SYSTEM_TIME on table failed

sql serversql-server-2016t-sqltemporal-tables

I have:

  • table with existing data
  • SQL Server 2016 SP1
  • SQL Server Management Studio 17.5

I am using the following statement to make a make my table a temporal one:

ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]
ADD [SysStartTime] DATETIME2(0) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_AnalysisCustomRollupsV2JoinGroups_SysStart DEFAULT GETUTCDATE()  
   ,[SysEndTime] DATETIME2(0) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_AnalysisCustomRollupsV2JoinGroups_SysEnd DEFAULT CONVERT(DATETIME2(0), '9999-12-31 23:59:59'),   
PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime]); 

ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]   
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.AnalysisCustomRollupsV2JoinGroupsChanges));

The issue:

On my local SQL instance I have many databases; it's very strange that the query runs successfully on some of them, and on some of them it gives me the following error:

Msg 13542, Level 16, State 0, Line 51 ADD PERIOD FOR SYSTEM_TIME on
table 'dbo.AnalysisCustomRollupsV2JoinGroups’ failed because there are
open records with start of period set to a value in the future.

Sometimes, when I am debugging/executing the query, the initial query runs successfully.

I read, that this could be because I have existing data in the table. So, I have change the logic like this:

  • create buffer table and populated it with all records
  • delete the records from the original table
  • create the temporal table
  • move the records back and drop the buffer table

and again, on some databases it is OK, and on other is not. Trying to resolve the issue I have found the following:

For the StartDate I’ve specified the current UTC date – this could be
any date and time that’s not in the future, though note it should be a
UTC one. If I’d tried to use GETDATE, as I’m currently on British
Summer Time, I would get the following error: Msg 13542, Level 16,
State 0, Line 51 ADD PERIOD FOR SYSTEM_TIME on table
‘TestAudit.dbo.SomeData’ failed because there are open records with
start of period set to a value in the future.

What does the above means? I need to change the machine time? Or because my local machine is not on UTC time I am getting this error sometimes?

Best Answer

I think I have found how to fix my issue, but I am not going to accept this as answer as I am not able to explain what is causing the problem and guarantee this will work anytime. It's fix found after a lot of testing and I will be glad if someone can bring more light here.

I have never used datetime2 with precision. So, I went back to the source of this format datetime2(0) - Alter Non-Temporal Table to be System-Versioned Temporal Table. The only difference with the script I have been using was the date time function. I used GETUTCDATE() as I do not need to be so precised with datetime(0)(2018-03-15 07:21:02 for example) and in the example it is SYSUTCDATETIME(). So, I have changed it.

I have create a script which is dropping a database if exists, restoring a database from backup and then executing my code in a loop (as I said I get the error sometimes and it was very hard to reproduce it).

I have run a the script a lot of times and I was getting different number of fails (sometimes 70%, sometimes 50%, sometimes below):

enter image description here

I have found this Why is GETUTCDATE earlier than SYSDATETIMEOFFSET? discussion about differences between old and new date time functions. Then build the following query:

DECLARE @UTC DATETIME2(0) = GETUTCDATE();
DECLARE @SYSUTC DATETIME2(0) = SYSUTCDATETIME();

WHILE DATEPART(SECOND, @UTC) = DATEPART(SECOND, @SYSUTC)
BEGIN;
    SET @UTC  = GETUTCDATE();
    SET @SYSUTC  = SYSUTCDATETIME();
END;

SELECT @UTC AS [UTC]
      ,@SYSUTC AS [SYS UTC]
      ,DATEPART(SECOND, @UTC) AS [UTC sec]
      ,DATEPART(SECOND, @SYSUTC) AS [SYS UTC sec]
      ,CASE WHEN @UTC < @SYSUTC THEN 1 ELSE 0 END AS [TimeTravelPossible]
      ,CONVERT(DATETIME2(0), @UTC) AS [UTC date]
      ,CONVERT(DATETIME2(0), @SYSUTC) AS [SYS UTC date]
      ,IIF(CONVERT(DATETIME2(0), @UTC) = CONVERT(DATETIME2(0), @SYSUTC), 1, 0) AS [Are The Same];

I just wanted to check if I can get different datetime2(0) dates using sys and not sys date time function. And of course it is possible.

enter image description here

Maybe, the checks that the engine is making is doing something like this, comparing its current date with my newer date and this is causing the error - open records with start of period set to a value in the future.

I have change the script like this and executed 1 000 times last night - no errors were generated. So, I believe I have fixed this particular issue, but I can't be sure.

ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]
ADD [SysStartTime] DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_AnalysisCustomRollupsV2JoinGroups_SysStart DEFAULT SYSUTCDATETIME()  
   ,[SysEndTime] DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_AnalysisCustomRollupsV2JoinGroups_SysEnd DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),   
PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime]); 

ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]   
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.AnalysisCustomRollupsV2JoinGroupsChanges));
Related Question