When DML and DDL changes occur on a Temporal Table do the correlating changes in the History table get logged in the Transaction Log as well?

All we need is an easy explanation of the problem, so here it is.

When DML and DDL queries are executed against a Temporal Table, those changes are appropriately propagated to the correlating History table. E.g. an UPDATE to the Temporal Table results in a new record to be INSERTed into the History table to reflect that change.

Is that INSERT to the History table, as well as other propagated changes, logged in the Transaction Log?

Does that mean enabling Temporal Tables will effectively double the amount of data that goes through the Transaction Log (at least for a database with the Recovery Model set to Full)?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Yes, changes to the history table get logged.

If you think about the purpose of the transaction log, it’s to allow for recovery (via backups and restores, or the normal recovery process that occurs on database startup). If changes to the history table were not logged, then restoring a backup of a database with temporal tables would likely result in inconsistencies between the main table and the history table.

Here’s a small demo. The setup creates a temporal table, inserts a row, updates that row, and then clears the log.

USE [master];
GO

DROP DATABASE IF EXISTS [315097];
GO

CREATE DATABASE [315097];
ALTER DATABASE [315097] SET RECOVERY SIMPLE 
GO

USE [315097];
GO

CREATE TABLE dbo.Employee
(
    EmployeeID int NOT NULL, 
    [Name] nvarchar(100) NOT NULL, 
    Position varchar(100) NOT NULL, 
    Department varchar(100) NOT NULL, 
    [Address] nvarchar(1024) NOT NULL, 
    AnnualSalary decimal (10,2) NOT NULL, 
    ValidFrom datetime2 GENERATED ALWAYS AS ROW START, 
    ValidTo datetime2 GENERATED ALWAYS AS ROW END,

    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo),
    CONSTRAINT PK_Employee PRIMARY KEY (EmployeeID)
)
WITH 
(
    SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory)
);
GO

/*
INSERT an initial row to avoid a lot of "noise" in this test related to inserting
the very first row into these tables
*/
INSERT INTO dbo.Employee
    (EmployeeID, [Name], Position, Department, [Address], AnnualSalary)
VALUES
    (1, N'Josh', 'Trusted User', 'I.T.', N'123 Main St', 3.50);

UPDATE dbo.Employee
SET AnnualSalary = 4.00;
GO

/*
Clear the log
*/
BACKUP DATABASE [315097] TO DISK = N'NUL';
GO
CHECKPOINT;
GO
/*
Query showing only checkpoints
*/
SELECT 
    l.[Current LSN],
    l.Operation,
    l.Context,
    l.AllocUnitName
FROM fn_dblog(NULL, NULL) l;

When DML and DDL changes occur on a Temporal Table do the correlating changes in the History table get logged in the Transaction Log as well?

Now if I update a row in the table and then run the same query against the log:

UPDATE dbo.Employee
SET AnnualSalary = 3.50;
GO

When DML and DDL changes occur on a Temporal Table do the correlating changes in the History table get logged in the Transaction Log as well?

Those 6 new log records are:

  • 2 for the begin / end of the implicit transaction
  • 2 for updating the differential backup bitmap
  • 1 for the update to the base table
  • 1 for the "hidden" insert into the history table

If I change the CREATE TABLE statement to remove the system versioning related items, and re-run the demo, the final state of the log looks like this:

When DML and DDL changes occur on a Temporal Table do the correlating changes in the History table get logged in the Transaction Log as well?

Does that mean enabling Temporal Tables will effectively double the amount of data that goes through the Transaction Log

It won’t exactly be double (inserts into the main table don’t make any changes to the history table), but yes there will be an increase in log throughput for sure.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply