This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(227.2, 71%, 32%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EOG%3C/text%3E%3C/svg%3E)
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.
Consider the usp_CleanupHistoryData procedure:
Maybe you can adjust it, or use the mentioned Retention Policy.
System Versioning cannot be altered for Ledger Tables.
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.
Right. Thanks.
Probably I should look into temporal table. It might be enough for our requirements.
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
This will not remove data from History table and it still takes a lot of GB on a disk.
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".
You can not remove any data from History table
You can not remove any data from History table.
You can not remove any data from History table.
Then I must say, your post is more then unclear; please provide (much) more details.
So, I have a Ledger table. Let's name it 'MyDataTable' It means you have 'MyDataTable' with your data and 'MyDataTableHistory' with history of all changes to this data in the main table.
Each insert/update/delete operation will add new records to History table.
I have 1 mil records in MyDataTable table. After 30 updates for All records in MyDataTable table, I have 16Gb history table size.
Now I don't need my MyDataTable and want to remove it from my database. When you do delete for Ledger table, it will be just renamed. Not deleted.
And I want to delete this table from database and reclaim disk space.
Then I must say, your post is more then unclear; please provide (much) more details.
There is nothing unclear with Oleg's post. Ledger tables is a feature in SQL Server it seems that you need to read up on.