database data delteion and space

Question

if we delete table A (10GB) from our database because we want to re-run the model and recreate the table (table B (also 10GB)), are we taking up 10GB in the server because we had deleted table A already or are we taking up 20GB in the server because table A hasn’t been fully freed up from the server without the shrinkage process so both table A and table B are taking up space?

Answer 1

You could be taking up more than 10 GB, yes. I believe that when you say DROP TABLE, the actual deallocation is deferred to the Ghost Cleanup process that runs as a background task. So if you load the table anew before you the clean has completed, you may not be able to reuse the pages from the old table.

I will need to confess that I am not 100% confident on this answer. I think that if you really want to know, you would need to test.

Answer 2

Hi @HuuM ,

if we delete table A (10GB) from our database because we want to re-run the model and recreate the table (table B (also 10GB))

Firstly, I would like to introduce the difference between DELETE and DROP.

For more details, please refer to Difference between DELETE and DROP SQL. If you don't want the table structure, you can choose the DROP statement.
Then we jump to the problem row:
Here are solutions:

1. Space that a table uses is not completely released after you use a DELETE statement to delete data from the table in SQL Server.
2. Don't set your database to auto-shrink. You need to do the shrinkage manually. After shrinking, you can rebuild the index for the fragmentation problem.

Best regards,
Seeya

---

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".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.