November 28, 2018 at 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?
November 28, 2018 at 7:58 am
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.
November 28, 2018 at 9:26 am
krypto69 - Wednesday, November 28, 2018 5:44 AMHI,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/
November 28, 2018 at 10:04 am
muthukkumaran Kaliyamoorthy - Wednesday, November 28, 2018 9:26 AMkrypto69 - Wednesday, November 28, 2018 5:44 AMHI,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.
November 28, 2018 at 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.
November 28, 2018 at 10:24 am
Jonathan AC Roberts - Wednesday, November 28, 2018 10:09 AMTRUNCATEONLY 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.
November 28, 2018 at 10:48 am
Steve Jones - SSC Editor - Wednesday, November 28, 2018 10:24 AMJonathan AC Roberts - Wednesday, November 28, 2018 10:09 AMTRUNCATEONLY 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.
November 28, 2018 at 1:01 pm
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
November 28, 2018 at 1:29 pm
November 30, 2018 at 5:49 am
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