Failed to set table system_version ON got error 'Cannot set SYSTEM_VERSIONING to ON when SYSTEM_TIME period is not defined and the LEDGER=ON option is not specified.'

Hi Zheng, Xiaohua •,

Welcome to Microsoft Q&A forum.

As I understand, you are getting failure while setting table system_version ON.

Could you please make sure you defined SYSTEM_VERSIONING and LEDGER on your table "UE.XXXXXX" while creation?

Converting regular tables to ledger tables isn't possible, but you can migrate the data from an existing regular table to a ledger table, and then replace the original table with the ledger table.

When you're performing a database ledger verification, the process needs to order all operations within each transaction. If you use a SELECT INTO or BULK INSERT statement to copy a few billion rows from a regular table to a ledger table, it will all be done in one single transaction. This means lots of data needs to be fully sorted, which will be done in a single thread. The sorting operation takes a long time to complete.

To convert a regular table into a ledger table, Microsoft recommends using the sys.sp_copy_data_in_batches stored procedure. This splits the copy operation in batches of 10-100 K rows per transaction. As a result, the database ledger verification has smaller transactions that can be sorted in parallel. This helps the time of the database ledger verification tremendously.

Refer:

https://video2.skills-academy.com/en-us/sql/relational-databases/security/ledger/ledger-how-to-migrate-data-to-ledger-tables?view=sql-server-ver16

and follow the steps.

If the table is existing System-Versioned Temporal table, then please try below if helps:

-- SET SYSTEM_VERSIONING TO OFF
ALTER TABLE [dbo].[MyTable]
SET (SYSTEM_VERSIONING = OFF)
GO



-- SET SYSTEM_VERSIONING TO ON
ALTER TABLE [dbo].[MyTable]
SET 
    (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[MyTable_Archive] , DATA_CONSISTENCY_CHECK = ON ))
GO

Hope this helps. Let us know if you have queries.

Thanks