0

I am going to run maintenance procedure similar to described in Cannot delete rows from a temporal history table .

The stored procedure will SET SYSTEM_VERSIONING = OFF , delete 3 million rows on temporal table and set it ON. It will take some time.

The question is how likely transactions on main table be impacted by running the stored procedure?
Am I right that selects on the main table will not be impacted?
What about inserts/ updates on the main table?

Information for reference from Microsoft documentation Stopping system-versioning on a system-versioned temporal table

When you set SYSTEM_VERSIONING = OFF and don't remove drop the SYSTEM_TIME period, the system continues to update the period columns for every insert and update operation.

0

1 Answer 1

1

If you are scared about locking you can always delete those rows in a loop preventing table lock

DECLARE @Rows INT SET @Rows = 1

WHILE (@Rows > 0) BEGIN
DELETE TOP (4500) FROM myTable
WHERE date < <date>

SET @Rows = @@ROWCOUNT END
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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