This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(89.60000000000001, 12%, 18%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ECS%3C/text%3E%3C/svg%3E)
I have created a testing temporal table with retention policy set to 1 days following the link
All requirement are met based on the documentation. However, the aged record is not removed automatically from history table.
running query select ValidTo from [dbo].[TestHistory] where ValidTo < DATEADD (day, -1, SYSUTCDATETIME())
return me the test historical record with ValidTo=2023-04-14T00:04:14.0000000 .
So the aged record does not get removed.
1.What is the process/job in azure SQL that cleans up temporal history table based on retnetion policy defined.
3.How can we see the run status of the job/process?
Thank you for your help.
Thank you for reply.
the URL provided is discussion not relevant to Azure SQL environment, but Regular SQL environment. MS documentation shows retention policy is supported on azure SQL and Regular SQL.
Hightlight as below:
Using temporal history retention policy approach
Applies to: SQL Server 2017 (14.x) and later versions, and Azure SQL Database.
Temporal history retention can be configured at the individual table level, which allows users to create flexible aging polices. Applying temporal retention is simple: it requires only one parameter to be set during table creation or schema change.
After you define the retention policy, the Database Engine starts checking regularly if there are historical rows that are eligible for automatic data cleanup.
Where can I get confirmation that Retention policy is not supported in azure SQL environment?
Or if it is actually supported in Azure SQL , what is the behavior?
Thanks
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(220.8, 4%, 31%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESM%3C/text%3E%3C/svg%3E)
Hi
Cuiwei (Syncia) Lu •,
Could you please let us know what Database you are using?
Is it Azure SQL Database or standalone SQL Server or some other Database?
Thanks in advance for the reply.
Hi ShaktiSingh
I am running my testing on Azure SQL database. I did see the retention policy work now. the original retention policy on the table was set as 6 months, i changed it to one day and expected aged data got deleted next day after pass the validTo threshhold. , but it didn't. It seems to me that the automatic clean up process does not run as frequently as I thought ,Probably run only once per day at 12:00 UTC time?
Go back to my original questions , In Azure SQL database enviornment
1.What is the process/job in azure SQL that cleans up temporal history table based on retnetion policy defined.
3.How can we check the status of the job/process > -in processing/complete/fail etc
Thank you
Hi
Cuiwei (Syncia) Lu •,
Welcome to Microsoft Q&A forum.
As I understand, Aged Data Not Removed in Azure SQL Temporal History Table.
Could you please refer to the similar Q&A post if this helps https://learn.microsoft.com/en-us/answers/questions/68851/aged-data-not-removed-in-sql-server-temporal-histo
Let us know if you have a different ask. Thanks
Hi
Cuiwei (Syncia) Lu •,
Thanks for confirming.
After you define retention policy, Azure SQL Database and Azure SQL Managed Instance starts checking regularly if there are historical rows that are eligible for automatic data cleanup. Identification of matching rows and their removal from the history table occur transparently, in the background task that is scheduled and run by the system. Age condition for the history table rows is checked based on the column representing end of SYSTEM_TIME period. If retention period, for example, is set to six months, table rows eligible for cleanup satisfy the following condition:
ValidTo < DATEADD (MONTH, -6, SYSUTCDATETIME())
Read here for more details: https://learn.microsoft.com/en-us/azure/azure-sql/database/temporal-tables-retention-policy?view=azuresql-mi
Hope this helps. Thanks