December 13, 2011 at 11:14 am
I spent the weekend performing a series of queries that removed around 16,000,000 rows from a table, thus freeing up nearly 200 Gb in a primary datafile, but I am unable to recover the space and return it to the OS. I have tried multiple shrink operations, and given it plenty of time for several full backups, but it's still holding on. When I attempt the T-SQL statement dbcc shrinkdatabase (mydb, truncateonly) I get the following message: File ID 1 of database ID 8 cannot be shrunk as it is either being shrunk by another process or is empty.
But I don't believe that it is being shrunk by another process. And if it is, it is taking days. The GUI shrink, which I ran yesterday, simply said it completed successfully.
Does anyone have any thoughts on this?
Thanks,
Tom
December 13, 2011 at 12:32 pm
Hi,
Can you try increase few MB's like 10 or 20MB for the File ID 1 then try shrinking.
Best Regards,
Rama Udaya.K
ramaudaya.blogspot.com
December 13, 2011 at 12:52 pm
That is a great tip. And it worked to some degree. Where there was 221 Gb of available space, now there is 141 Gb. Is it safe to repeat that procedure until I recover all the available space?
December 13, 2011 at 5:06 pm
thotvedt (12/13/2011)
That is a great tip. And it worked to some degree. Where there was 221 Gb of available space, now there is 141 Gb. Is it safe to repeat that procedure until I recover all the available space?
Pretty safe to assume it didn't.
Why do want to reclaim ALL the space? Dbs are there to save <new> data and they need to grow to do that when they don't have the space readily available.
Was there a blob in that table? If so you need to rebuild the CI before you can succeed with the shrink (leaving enough space for x months of data).
December 14, 2011 at 8:39 am
I don't necessarily need to reclaim all the space. But it's an 12 Gb db with over 140 Gb of unused space. And the table that was using all that space won't be growing as it did due to configuration changes. I will try your suggestion.
Thanks.
Tom
December 14, 2011 at 8:42 am
Ok, that's one of the good uses of shrink then!
😉
December 14, 2011 at 12:21 pm
Is it safe to repeat that procedure until I recover all the available space?
Sorry for the late reply,The which I said is for one of the option Because I have seen many cases it was same issues , hence I have opted to increase the space to some MB so that data file will get altered and then you can perform the shrink option, how ever it varies depends on the situation & what the database buffer holds internally (If you want to know more about that I can send you information via -my personal ID:rama38udaya@gmail.com:hehe:) & also the version matters.
Since in your case it was good to know that ;-)"I don't necessarily need to reclaim all the space. But it's an 12 Gb db with over 140 Gb of unused space. And the table that was using all that space won't be growing as it did due to configuration changes".
Additional info(Just an FYI):
How ever if you shrink the datafile that may lead to the fragmentation & may hinder query performance issue hence it is not recommending to shrink the datafile(you can refer Paul Randal blog).
In this type of error you may need to verify before taking any actions-
1.Check whether the Auto_shrink set to ON.
2.check any Stored procedure internally does the shrinking activity on the database file level(this is just an example because we don't know what Developer puts or Business people puts depending on their transaction activities without knowing the impact of Frag......).
3.Any SQL agent jobs scheduled to perform the shrinking activity.
Best Regards,
Rama Udaya
ramaudaya.blogspot.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply