March 27, 2014 at 6:26 am
We have a little problem where our database has grown far too much and we'd like to recover (most of the) space.
This database contains mostly one BIG table and whoever designed it decided to have a clustered primary key on a UNIQUE IDENTIFIER... :blush:
We have no option to change the code using that table so we must keep the "interface".
So, I created a copy of that table on a new (default) filegroup with a clustered index on the created datetime and created a (unclustered) unique index on the Unique Identifier.
Then, I just swapped both table names so that I have practically the same table structure but with a useful clustered index and on a different filegroup.
Because of the size, I only copied one month of data into the new table.
I unfortunately discovered that I am not allowed to drop the Primary File / Filegroup... :crying:
Problem: how do we recover the space from that database without shrinking it...
My best idea on doing this is to drop the old huge table (still on the primary filegroup) when we don't need it anymore (in a couple of months) and then shrink this initial filegroup to a minimal size.
I know shrinking files has a very bad reputation but would it matter on a file virtually empty and not used anymore?
What do you think?
Do I need to keep this initial file to a certain size?
Is it daft? :doze:
Do you see a better way to recover my disk space? 🙂
Thanks
Eric
March 27, 2014 at 7:17 am
Shrinking a file is the only way you will regain the space that file is using.
After shrinking, be sure to rebuild your indexes on that file as they will most likely be showing at 99% fragmentation. This is why shrinking files regularly is not recommended (as far as I understand, a shrink takes the last used page in the file and moves it to the first available space, so effectively rebuilds your indexes backwards) along with the fact that your database is likely to grow and so having the space pre created is better practise.
Once you have shrunk the file and rebuilt your indexes, I would then recommend growing the file to a sensible size for future growth, in order to allow space for your tables to grow into.
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
March 28, 2014 at 9:03 am
Eric Mamet (3/27/2014)
We have a little problem where our database has grown far too much and we'd like to recover (most of the) space.This database contains mostly one BIG table and whoever designed it decided to have a clustered primary key on a UNIQUE IDENTIFIER... :blush:
We have no option to change the code using that table so we must keep the "interface".
So, I created a copy of that table on a new (default) filegroup with a clustered index on the created datetime and created a (unclustered) unique index on the Unique Identifier.
Then, I just swapped both table names so that I have practically the same table structure but with a useful clustered index and on a different filegroup.
Because of the size, I only copied one month of data into the new table.
I unfortunately discovered that I am not allowed to drop the Primary File / Filegroup... :crying:
Problem: how do we recover the space from that database without shrinking it...
My best idea on doing this is to drop the old huge table (still on the primary filegroup) when we don't need it anymore (in a couple of months) and then shrink this initial filegroup to a minimal size.
I know shrinking files has a very bad reputation but would it matter on a file virtually empty and not used anymore?
What do you think?
Do I need to keep this initial file to a certain size?
Is it daft? :doze:
Do you see a better way to recover my disk space? 🙂
Thanks
Eric
Why did you create a copy of the table instead of just moving it to a new filegroup, this would leave free space in the primary filegroup\file which you could then shrink from that one primary file
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 28, 2014 at 9:20 am
Because the table was pretty big and I was not allowed any downtime...
...Plus I did not think about this at the time! 😀
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply