Sample Header Ad - 728x90

Is there any reasonable way to Purge Expired Data from a SQL Server Ledger Database

3 votes
1 answer
136 views
The SQL Server Ledger Database functionality looks very interesting, and I would like to recommend it for several use cases, but I am concerned that there is no option for purging historical data. I understand the logic, because "Purging History" could easily be abused to cover your tracks. That said, purging historical data is a legitimate business requirement, even if, and sometimes, especially when, dealing with specific PII data. Currently, the only option I see is to create a whole new Ledger Database and copy forward all data and structures that need to be retained. The big issue with that solution is that it results in the loss of ALL historical audit/security information, even for data that is not going to be purged. As a result, if a business wanted to use the Ledger tables, but purge expired data after 7 years, purging yearly, then it would need to retain 6 copies of previous ledger database backups to access the old audit/security information. Currently, the only GOOD solution I can see is if Microsoft creates a new piece of functionality that allows for the rebuilding of an existing Ledger Database while dropping all history and historical data beyond a specific point. (e.g. 5 years) Am I missing something? --- In short, Temporal Tables are cool. I love the ability to set a HISTORY_RETENTION_PERIOD directly, but they very much lack the security features of LEDGER tables. You can mess with the history, and dropping a column also drops all of its history. Also missing is an Append-Only table option. Basically, Temporal tables can't satisfy the Audit requirements that Ledger Database/Tables can. Mutable and purgeable are two very different things. To summarize, I don't want a mutable history. On updating temporal table history see: https://stackoverflow.com/q/46737825
Asked by AnthonyVO (135 rep)
Jul 23, 2025, 08:30 PM
Last activity: Jul 28, 2025, 12:41 AM