New System Versioned tables created on their own?

Question

In SQL Server, I have System Versioned tables of my Dimension tables, IE: Dim.Entity with a named history table called Dim.EntityHistory. However, I've noticed recently that on a few of these tables, SQL Server has seemingly made new System Versioned tables on its own (IE: MSSQL_TemporalHistoryFor_12928938) and "kicked" my named history tables out into the general table area. Does anyone have any insight into why this happened? From what I understand in the documentation, if you update the table schema, the change is propagated to the history table automatically (I initially thought this might be why this happened).

I am running on SQL Server 2019 / Azure.

Answer 1

Thanks to @Stu for the answer above. I actually found in the documentation where they describe exactly what you need to do. To solve the problem for my example above:

BEGIN TRY

   BEGIN TRAN

   ALTER TABLE Dim.EntityName SET (SYSTEM_VERSIONING = OFF);
   --Do Activity here IE: Truncate
   ALTER TABLE Dim.Entity SET
   (
      SYSTEM_VERSIONING = ON (HISTORY_TABLE = Dim.EntityHistory)
   );

   COMMIT TRAN;

END TRY
BEGIN CATCH

IF @@TRANCOUNT > 0
    ROLLBACK;

  SELECT ERROR_NUMBER() AS ErrorNumber
        ,ERROR_STATE() AS ErrorState
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

END CATCH;

Adapted from the article below:

When turning system versioning back on, do not forget to specify the HISTORY_TABLE argument. Failing to do so will result in a new history table being created and associated with the current table. The original history table will still exist as a normal table, but won't be associated with the current table.

https://docs.microsoft.com/en-us/sql/relational-databases/tables/stopping-system-versioning-on-a-system-versioned-temporal-table?view=sql-server-ver15