Can transactions on main table be delayed/deadlock when maintenance transaction on temporal table runs?
Analeea last edited by
I am going to run maintenance procedure similar to described in https://stackoverflow.com/questions/53746197/cannot-delete-rows-from-a-temporal-history-table/53749664#53749664 .
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 https://docs.microsoft.com/en-us/sql/relational-databases/tables/stopping-system-versioning-on-a-system-versioned-temporal-table?view=sql-server-ver16#important-remarks
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.
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 <
SET @Rows = @@ROWCOUNT END