Temporal Tables are awesome.
The problem with temporal tables is that they produce a lot of data. Every row-level change stored in the temporal table's history table quickly adds up, increasing the possibility that a low-disk space warning is going to be sent to the DBA on-call.
In the future with SQL Server 2017 CTP3, Microsoft allows us to add a retention period to our temporal tables , making purging old data in a temporal table as easy as specifying:
ALTER DATABASE DatabaseName SET TEMPORAL_HISTORY_RETENTION ON CREATE TABLE dbo.TableName ( ... ) WITH ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.TableNameHistory, HISTORY_RETENTION_PERIOD = 6 MONTHS ) );
However, until we are all on 2017 in production, we have to manually automate the process with a few scripts.
Purging old data out of history tables in SQL Server 2016
In the next few steps we are going to write a script that deletes data more than a month old from my CarInventoryHistory table :
SELECT * FROM dbo.CarInventory; SELECT * FROM dbo.CarInventoryHistory;
And now if we write our DELETE statement:
ALTER TABLE dbo.CarInventory SET ( SYSTEM_VERSIONING = OFF ) ; GO -- In the real-world we would do some DATE math here DECLARE @OneMonthBack DATETIME2 = '2017-06-04'; DELETE FROM dbo.CarInventoryHistory WHERE SysStartTime < @OneMonthBack;
You'll notice that we first had to turn system versioning off: SQL Server won't let us delete data from a history table that is currently tracking a temporal table.
This is a poor solution however. Although the data will delete correctly from our history table, we open ourselves up to data integrity issues. If another process INSERTs/UPDATEs/DELETEs into our temporal table while the history deletion is occurring, those new INSERTs/UPDATEs/DELETEs won't be tracked because system versioning is turned off.
The better solution is to wrap our ALTER TABLE/DELETE logic in a transaction so any other queries running against our temporal table will have to wait:
-- Run this in query window #1 (delete data): BEGIN TRANSACTION; ALTER TABLE dbo.CarInventory SET ( SYSTEM_VERSIONING = OFF ); GO -- In the real-world we would do some DATE math here DECLARE @OneMonthBack DATETIME2 = '2017-06-04'; DELETE FROM dbo.CarInventoryHistory WITH (TABLOCKX) WHERE SysStartTime < @OneMonthBack; -- Let's wait for 10 seconds to mimic a longer delete operation WAITFOR DELAY '00:00:10'; --Re-enable our SYSTEM_VERSIONING ALTER TABLE dbo.CarInventory SET ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CarInventoryHistory)); GO COMMIT TRANSACTION; -- Run this in query window #2 during the same time as the above query (trying to update during deletion): UPDATE dbo.CarInventory SET InLot = 0 WHERE CarId = 4;
And the result? Our history table data was deleted while still tracking the row-level data changes to our temporal table:
All that is left to do is to throw this script into a SQL Agent job and schedule how often you want it to run.