Deleted tables - release space

  • HI,

    We deleted a bunch of tables (moved them to cheaper storage SAN) and now when I run 'DBCC SHRINKFILE (N'mcr_dc_new_Data' , 0, TRUNCATEONLY)'
    it won't shrink (release). This is in a high availability setup, but I don't think that matters.

    I have backed it up many times, backed up the t logs, etc. Can't get it to give me the space back.

    What can I try/look for next?

  • how big is the file?,  may be create another file and move everything  into it, then shrink it and either move back data or just rename the new one and drop old one.

  • krypto69 - Wednesday, November 28, 2018 5:44 AM

    HI,

    We deleted a bunch of tables (moved them to cheaper storage SAN) and now when I run 'DBCC SHRINKFILE (N'mcr_dc_new_Data' , 0, TRUNCATEONLY)'
    it won't shrink (release). This is in a high availability setup, but I don't think that matters.

    I have backed it up many times, backed up the t logs, etc. Can't get it to give me the space back.

    What can I try/look for next?

    Have you checked the free space available within the file.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • muthukkumaran Kaliyamoorthy - Wednesday, November 28, 2018 9:26 AM

    krypto69 - Wednesday, November 28, 2018 5:44 AM

    HI,

    We deleted a bunch of tables (moved them to cheaper storage SAN) and now when I run 'DBCC SHRINKFILE (N'mcr_dc_new_Data' , 0, TRUNCATEONLY)'
    it won't shrink (release). This is in a high availability setup, but I don't think that matters.

    I have backed it up many times, backed up the t logs, etc. Can't get it to give me the space back.

    What can I try/look for next?

    Have you checked the free space available within the file.

    Yes. It shows the free space is available. I changed it to run this :
    DBCC SHRINKFILE (N'mcr_dc_new_Data' , 400000)

    and it ran for hours....I thought it's got it now. But it still did not release the space.

  • TRUNCATEONLY will only shrink the file if it has free space at the end of the file.
    You could try to shrink it several times reducing the size by a few GB on each go.

  • Jonathan AC Roberts - Wednesday, November 28, 2018 10:09 AM

    TRUNCATEONLY will only shrink the file if it has free space at the end of the file.
    You could try to shrink it several times reducing the size by a few GB on each go.

    I do this, working in batches when needed.

  • Steve Jones - SSC Editor - Wednesday, November 28, 2018 10:24 AM

    Jonathan AC Roberts - Wednesday, November 28, 2018 10:09 AM

    TRUNCATEONLY will only shrink the file if it has free space at the end of the file.
    You could try to shrink it several times reducing the size by a few GB on each go.

    I do this, working in batches when needed.

    I tried shrinking it a few hundred megs - didn't release. I checked for ghost records - very few. I'm stumped, but wondering more and more if it's something to do with High Availability.

  • it is possible, one way to check is to remove secndory replica (s) from AG , shrinkfile and then add back secondary replica(s), they should catch up to primary

  • Thanks everyone. It wasn't ghost records. 

    I tried restoring the DB in a non H/A sever- it released the space.

    So I tried again in PROD - for some reason it took five runs of the shrink command but then it released...very odd..perhaps latency between remote listeners? dunno

Viewing 10 posts - 1 through 9 (of 9 total)

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