January 5, 2009 at 11:30 am
All the filegroups which I created has data range stored. Right now , all the filegroups are using 40% space and 50% to 60% space is free on lots of filegroups. Those file groups will not be updated because data will not fall in that range.
My question is how to retain that free space from all the filegroups so that I can have more Hard Disk space on the server. I know that we can increase the size of the filegroups but cannot decrease the size.
Is there a better way other than creating a new filegoups and than migrate all the data.
January 5, 2009 at 12:23 pm
It's going to be a bit intensive, but you can shrink the files with a DBCC SHRINKFILE
January 5, 2009 at 1:21 pm
Yes, Shrinking the whole database file will be very intensive. Can we shrink particular file group ?
January 5, 2009 at 1:30 pm
USE Databasename
GO
DBCC SHRINKFILE (Databasename_data, 1)
GO
I put " 1 " as target in most of the time since it will take the nearest value to shrink that file.
SQL DBA.
January 5, 2009 at 1:49 pm
One thing to be careful of is fragmentation. Even if this data will not get used, you probably want it with a high fill factor and not fragmented. Shrinking increases fragmentation.
what I might recommend is that you shrink, then rebuild the indexes with a new fillfactor. Make sure that the files will autogrow, and if they grow a little, I'd leave them there. A little wasted space for better performance is probably worth it.
January 5, 2009 at 3:57 pm
Thanks for all the reply.
Let me explain again. I have created a partitioned table and the data is stored in the file groups based on the range. Some of the ranges are old and data will never fall in that range , other words that file group will never grow. for example.
There are 10 file groups
F1
F2
F3
..
F10
File group F1 stores data from Jan 2007 1 to Jan 2007 31
File group F2 stores data from Feb 2007 1 to Feb 2007 28
File group F3 stores data from Mar 2007 1 to Mar 2007 31
Filegroup F1 was created with 100 MB and is used only 50 MB
Filegroup F2 was created with 100 MB and is used only 60 MB
Filegroup F3 was created with 100 MB and is used only 30 MB
as we know that data from those filegroups are old and never been updated so we need to claim space from those filegroups. I don't want to shrink current file group because it is being used currently. If I shrink the whole database file than my current filegroup will also be shrinked and also I don't want to shrink the whole database because of the huge size. the size of the databse is 1 Tera byte.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply