database data delteion and space

HuuM 46 Reputation points
2022-10-26T14:11:27.727+00:00

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?

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

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 92,961 Reputation points
    2022-10-26T21:34:37.873+00:00

    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.

    0 comments No comments

  2. Seeya Xi-MSFT 16,406 Reputation points
    2022-10-27T05:50:49.64+00:00

    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.
    254581-image.png
    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.

    0 comments No comments