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

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.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar9mo ago
Accepted 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:
1. Export/copy the data you need out of the ledger-enabled database,
2. Drop the ledger-enabled database,
3. Recreate a new database without LEDGER = ON, and
4. Import the data back.
+2
Pablo Lartinez9mo ago
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?
0
Deepak Tewatia9mo ago
Thanks Jignesh and Vishal
+1
Vishal Gami10mo ago
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.
+1