How to set ledger=off in ledger enabled sql server DB?

Question

How to set ledger=off in ledger enabled sql server DB

Jignesh Kumar · Answer

Hello Deepak, You can’t disable the ledger feature on a database once it’s enabled. You would have to drop the database and recreate it. If you need a non-ledger version of that database (or want to “turn off” ledger), your only clean option is: Export/copy the data you need out of the ledger-enabled database, Drop the ledger-enabled database, Recreate a new database without LEDGER = ON , and Import the data back.

Vishal Gami · Answer

It is not possible to disable the ledger feature on a SQL Server or Azure SQL database after it has been enabled . The ledger functionality is a permanent, one-way setting by design to ensure data integrity and immutability. Understanding Ledger Immutability The core purpose of the ledger feature is to provide a tamper-evident audit trail of all data modifications, which is achieved by making the data and its history immutable. Allowing the ledger to be turned off would defeat this security objective. Alternatives if You No Longer Need the Ledger If your requirements have changed and you no longer need the ledger functionality for a specific set of data or tables, the recommended approach is to migrate your data to a new, non-ledger database or table. Create a new, regular database : You can create a new database without the WITH LEDGER = ON clause. Migrate data : Copy the necessary data from your ledger tables to regular tables in the new database. Decommission the old database : Once you have successfully migrated and verified your data in the new database, you can decommission the original ledger-enabled database. Important Limitations No T-SQL Command to Disable : There is no ALTER DATABASE SET LEDGER = OFF command. Table-Level Restrictions : You cannot convert a ledger table back into a non-ledger table. Data Deletion Constraints : You cannot delete or modify data in ledger history tables (or the dropped data in ledger tables). For more details on the limitations of the ledger feature, refer to the official Microsoft Learn documentation on Ledger considerations and limitations .

Deepak Tewatia · Answer

Thanks Jignesh and Vishal

Pablo Lartinez · Answer

Ever dealt with a massive SQL Server database for auditing? I did, and performance tanked! Moving historical audit data to a separate read-only DB dramatically improved query speeds. Now, with SQL Server's ledger feature, I wonder: could it streamline this entire process and eliminate the need for manual archival?

How to set ledger=off in ledger enabled sql server DB?

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (4)