-1

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

2
  • 1
    Yes, it does, all changes to any table are logged. You can test this by looking at the undocumented fn_dblog system function. Full vs Simple recovery makes no difference, Simple just allows early checkpointing. Aug 2 at 0:41
  • @Charlieface Thanks! You should put that in an answer. 😉
    – J.D.
    Aug 2 at 1:57

1 Answer 1

2

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;

Screenshot of query results in SSMS showing only 3 log records related to the CHECKPOINT operation

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

Screenshot of SSMS query results showing six new log records related to updating the main table and inserting into the history table

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:

Screenshot of query results in SSMS showing only 4 new log records related to the update to the main table

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.

1
  • 1
    Excellent, thank you for the answer. I suspected so (makes sense from a recovery perspective, as you mentioned), but when combing through the books online I didn't find any mention of it.
    – J.D.
    Aug 3 at 17:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.