Início / dba / Perguntas / 200079
Accepted
gotqn
Asked: 2018-03-14 02:05:29 +0800 CST

ADD PERIOD FOR SYSTEM_TIME na tabela falhou

  • 772

Eu tenho:

  • tabela com dados existentes
  • SQL Server 2016 SP1
  • SQL Server Management Studio 17.5

Estou usando a seguinte declaração para tornar minha tabela temporal:

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));

O problema:

Na minha instância SQL local tenho muitos bancos de dados; é muito estranho que a consulta seja executada com sucesso em alguns deles, e em alguns deles me dá o seguinte erro:

Msg 13542, Level 16, State 0, Line 51 ADD PERIOD FOR SYSTEM_TIME na tabela 'dbo.AnalysisCustomRollupsV2JoinGroups' falhou porque há registros abertos com início de período definido para um valor no futuro.

Às vezes, quando estou depurando/executando a consulta, a consulta inicial é executada com êxito.

Eu li, que isso pode ser porque eu tenho dados existentes na tabela. Então, eu mudei a lógica assim:

  • crie uma tabela de buffer e a preencha com todos os registros
  • excluir os registros da tabela original
  • criar a tabela temporal
  • mova os registros de volta e elimine a tabela de buffer

e novamente, em alguns bancos de dados está OK, e em outros não. Tentando resolver o problema encontrei o seguinte:

Para StartDate, especifiquei a data UTC atual – pode ser qualquer data e hora que não seja no futuro, embora observe que deve ser UTC. Se eu tentasse usar GETDATE, pois estou atualmente no horário de verão britânico, obteria o seguinte erro: Msg 13542, Level 16, State 0, Line 51 ADD PERIOD FOR SYSTEM_TIME on table 'TestAudit.dbo.SomeData' falhou porque há registros abertos com início de período definido para um valor no futuro.

O que significa acima? Preciso alterar o horário da máquina? Ou porque minha máquina local não está no horário UTC, estou recebendo esse erro às vezes?

sql-server t-sql

4 respostas

  1. Best Answer

    Acho que descobri como resolver meu problema, mas não vou aceitar isso como resposta, pois não posso explicar o que está causando o problema e garantir que isso funcionará a qualquer momento. A correção foi encontrada após muitos testes e ficarei feliz se alguém puder trazer mais luz aqui.

    Nunca usei datetime2com precisão. Então, voltei para a fonte deste formato datetime2(0)- Alter Non-Temporal Table to be System-Versioned Temporal Table . A única diferença com o script que tenho usado foi a função de data e hora. Usei GETUTCDATE()como não preciso ser tão preciso com datetime(0)( 2018-03-15 07:21:02por exemplo) e no exemplo é SYSUTCDATETIME(). Então, eu mudei.

    Eu criei um script que está descartando um banco de dados, se existir, restaurando um banco de dados do backup e, em seguida, executando meu código em um loop (como eu disse, às vezes recebo o erro e foi muito difícil reproduzi-lo).

    Eu executei o script muitas vezes e estava recebendo um número diferente de falhas (às vezes 70%, às vezes 50%, às vezes abaixo):

    insira a descrição da imagem aqui

    Eu encontrei isso Por que GETUTCDATE é anterior a SYSDATETIMEOFFSET? discussão sobre as diferenças entre as funções de data e hora antigas e novas. Em seguida, crie a seguinte consulta:

    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];

    Eu só queria verificar se posso obter datetime2(0)datas diferentes usando sys e não a função sys date time. E claro que é possível.

    insira a descrição da imagem aqui

    Talvez, as verificações que o motor está fazendo esteja fazendo algo assim, comparando sua data atual com a minha data mais recente e isso está causando o erro -open records with start of period set to a value in the future.

    Eu alterei o script assim e executei 1.000 vezes ontem à noite - nenhum erro foi gerado. Então, acredito ter corrigido esse problema específico, mas não posso ter certeza.

    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));
    • 7

  2. A verdadeira razão por trás desse problema é porque o SQL Server não trunca a precisão indesejada de datetime/ datetime2, ele a arredonda !

    Para mostrar isso de forma simples, eu lancei um valor de tempo com milissegundos igual ou maior que 500

    SELECT CAST('10:10:59.563' AS TIME(0))

    O resultado é: 10:11:00, que é obviamente maior que 10:10:59.563

    O mesmo se aplica a DATETIME(n).

    No seu exemplo original, você está pegando GETUTCDATE(), que retorna um DATETIME, que tem uma precisão de 3(ish). Logo de cara, você está obtendo uma data e hora arredondada que às vezes será arredondada para o futuro. Você enfrentará o mesmo problema de conversão com menor precisão de SYSUTCDATETIME, por exemplo CAST(SYSUTCDATETIME() AS DATETIME2(3))- onde o 4º dígito fracionário é '5' ou superior, a data é arredondada para o futuro.

    Para mitigar isso, você pode compensar o arredondamento DATEADDpara truncar efetivamente a precisão indesejada, por exemplo

    CAST(DATEADD(MILLISECONDS, -500, SYSUTCDATETIME()) AS DATETIME(0))
CAST(DATEADD(MILLISECONDS, -50, SYSUTCDATETIME()) AS DATETIME(1))
CAST(DATEADD(MILLISECONDS, -5, SYSUTCDATETIME()) AS DATETIME(2))
CAST(DATEADD(MICROSECONDS, -500, SYSUTCDATETIME()) AS DATETIME(3))
CAST(DATEADD(MICROSECONDS, -50, SYSUTCDATETIME()) AS DATETIME(4))
CAST(DATEADD(MICROSECONDS, -5, SYSUTCDATETIME()) AS DATETIME(5))

    Como isso funciona:

    Se quisermos truncar até duas casas decimais (DP), precisamos subtrair metade do menor valor diferente de zero (por exemplo, 0,01 / 2 = 0,005) do valor antes do arredondamento.

    Alguns exemplos:

    7.004999999999 - 0.005 = 6.999999999999 round to two DP = 7.00
7.000999999999 - 0.005 = 6.995999999999 round to two DP = 7.00
7.009999999999 - 0.005 = 7.004999999999 round to two DP = 7.00

    Portanto, para editar o código fornecido, a maneira correta de usar DATETIME(0)nessa situação seria:

    ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]
  ADD [SysStartTime] DATETIME2(0) GENERATED ALWAYS AS ROW START HIDDEN
          CONSTRAINT DF_AnalysisCustomRollupsV2JoinGroups_SysStart 
              DEFAULT CONVERT(DATETIME(0), DATEADD(MILLISECONDS, -500, SYSUTCDATETIME())),
      [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
));
    • 2

  3. Eu recebi esse erro ao atualizar muitas tabelas em uma transação (por meio de uma migração de estrutura de entidade) Parece que eu precisava atualizar as tabelas em ordem de integridade referencial, fazendo as tabelas filhas primeiro.

    Eu divido o processo em várias migrações.

    • 0

  4. Isso funcionou no ambiente SQL Server e Azure SQL (PaaS).

    ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]
     ADD SysStartTime datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN 
         CONSTRAINT DF_SysStart DEFAULT DATEADD(second, -1, SYSUTCDATETIME()),
     SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN 
         CONSTRAINT DF_SysEnd DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59'),
     PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);
GO

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

GO
    • -1

relate perguntas