Post reply

Space is not getting freed up when deleting data from a log table.

  • August 23, 2023 at 11:18 am

    Go to Answer

    #4269029

    Hi All,

     

    We are trying to delete data from a large 5TB log table. We are able to delete the data but that is not shown up in the free space nor released to OS. The table has ntext columns as well. Application stores some xml documents in this Log table.

    Want to know why the Deletes are not freeing up the space?

    Drop and truncate on the other hand is showing up in free space.

     

    Regards.

    Sam

     

  • August 23, 2023 at 12:08 pm

    Answer

    #4269061

    Once a file size is allocated within SQL Server, it stays allocated. You can delete every bit of data from a 5tb data file, and the data file will still be 5tb. If you need to deallocate the space, you need to look to shrink operations. You can shrink a file or a database. Here's an introduction to the topic. However, this comes with huge caveats. If this is a one time operation, no big deal. Do it and you're done. However, what you frequently see is people letting SQL Server allocate extra space over & over, then delete data, then truncate the files, repeatedly, frequently even automatically. Do NOT do this. Just don't. Don't do it. Here's one explanation for why. There are tons more.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • August 23, 2023 at 8:05 pm

    #4269436

    Thank you Sir.

  • August 24, 2023 at 12:39 am

    #4269595

    vsamantha35 wrote:

    Hi All,

    We are trying to delete data from a large 5TB log table. We are able to delete the data but that is not shown up in the free space nor released to OS. The table has ntext columns as well. Application stores some xml documents in this Log table.

    Want to know why the Deletes are not freeing up the space?

    Drop and truncate on the other hand is showing up in free space.

    Regards.

    Sam

    You have to delete ALL of the rows from a given "page" for it to be considered to be released to "free space".  In most cases, though, you have to delete all rows from an "extent" (8 related pages) for it to be auto-magically deallocated and released to "free space".

    If you can't guarantee that all rows of extents will be deleted, then simply do some index maintenance on your clustered and non-clustered indexes that are showing low page density.  Personally, I hate using REORGANZE but this may be one of just two places where it will help more than hurt (and it usually hurts a lot... people just don't know it).  You might also have to use it more than once to get the full effect depending on the deletes that were done.

    You do have to be careful about REBUILDs because anything >= 128 extents (just 8 MB) is going to cause a new copy of the index to be built and then the old copy will be dropped.  That could leave more freespace than what you're trying to save.

    A bigger question, though, is... how much of the log table are you trying to delete?  DELETE is probably NOT going to be your friend here but won't waste your time until you tell us how much space are you trying to get the log table down to.

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 24, 2023 at 8:11 am

    #4269882

    We are trying to delete data from logging table which has grown upto 7 TB. App team now decided to delete the data based on some filters. As they are performing the deletes, we see Exclusive locks held on that particular table resulting in heavy blocking. now we are trying to delete in smaller chunks. Doing so, the free space was not visible in the DMVs. So, had the doubt in mind how the deallocation happens in case of DELETEs. For DROP and TRUNCATE, we can see the free space getting released immediately.

  • August 25, 2023 at 12:09 am

    #4270579

    vsamantha35 wrote:

    We are trying to delete data from logging table which has grown upto 7 TB. App team now decided to delete the data based on some filters. As they are performing the deletes, we see Exclusive locks held on that particular table resulting in heavy blocking. now we are trying to delete in smaller chunks. Doing so, the free space was not visible in the DMVs. So, had the doubt in mind how the deallocation happens in case of DELETEs. For DROP and TRUNCATE, we can see the free space getting released immediately.

    Understood.  I'm asking about some details to maybe help ease the pain here.  How many bytes do you want to delete from the table and what is the Clustered Index for the table?

    For example, if you folks are  trying to delete 5TB of a 7TB table using DELETEs, you're probably doing it wrong and you're probably missing an opportunity to do an incredible bit of future proofing where such deletes in the future can be done in milliseconds with virtually no blocking.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply