Completely remove Ledger Table from Database

Question

Is there a way how to completely remove Ledger table from a database and reclaim disk space?

History table can take gigabytes, and I want to remove all the data related to some ledger table. Data and history.

But I don't see how it can be done.

Answer 1

Since the whole purpose of Ledger is to keep all data and track all changes in a way that data cannot be tampered with, at least not without being detected, deletion of data is not possible. That would defeat the purpose of the ledger.

If your organisation no longer have the need of the ledger table, you would have to create a new database and copy the remaining data over.

Answer 2

I want to remove all the data

I don't understand, what the problem here is doing so?

Issue a DELETE command.

https://learn.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sql?view=sql-server-ver16

Answer 3

Hi @Oleg Gelya

When rows are removed from a table, that space is simply marked as unused inside the datafile.

Datafiles do not shrink automatically (unless auto-shrink is turned on), which means when "deleted" are not physically removed from the file - just marked as "free" inside of SQL Server through the use of bitmap and bytemap allocation structures and SQL Server will re-use the space accordingly.

See this similar thread: Why does the size of the datafile on disk not decrease after deleting records from a table?

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".