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.
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.
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 ; GO CREATE DATABASE ; ALTER DATABASE  SET RECOVERY SIMPLE GO USE ; 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  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;
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
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:
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 🙂