Can transactions on main table be delayed/deadlock when maintenance transaction on temporal table runs?



  • 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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2