Completely remove Ledger Table from Database

Oleg Gelya 20 Reputation points
2024-05-31T07:38:46.19+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,111 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 103.9K Reputation points MVP
    2024-05-31T21:23:10.2733333+00:00

    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.


2 additional answers

Sort by: Most helpful
  1. Olaf Helper 42,571 Reputation points
    2024-05-31T07:41:50.3666667+00:00

    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


  2. LiHongMSFT-4306 24,926 Reputation points
    2024-05-31T08:57:52.0833333+00:00

    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".