Timeout during SET SYSTEM_VERSIONING = ON with large table [closed]

Question

I'm using the temporal functionality on quite a large table with 18 million records. For GDPR compliancy I have implemented a way to turn versioning off, delete the records related to the customer that wants to protect his privacy and turn versioning back on. This obviously wouldn't be necessary if deleting records were allowed in a history table. I can see why they didn't allow it, though I don't understand why an option to allow it wasn't implemented.

Turning versioning back on poses the problem. It takes a lot longer than expected, leading to timeouts. Seemingly directly correlated with the amount of records in the temporal table. It takes more than 30 seconds to turn versioning back on and this table is only expected to grow. Is there a solution to this problem other than increasing the timeout?

Answer 1

Per the docs

When an existing history table is specified when enabling SYSTEM_VERSIONING, a data consistency check will be performed across both the current and the history table. It can be skipped if you specify DATA_CONSISTENCY_CHECK = OFF as an additional parameter.

This should only be used if you are confident the "after" state is definitely valid. Details of the checks it performs are here.

For example

alter table dbo.mytable
set (system_versioning = on (
  history_table = dbo.mytable_history,
  data_consistency_check = off
))