asked
Creating file groups and reclaiming empty space
I started out with a database file approx 900 GB in size. The database contains transaction information for 7 years. The data is stored in quarterly tables. XXXX_2003_0101, XXXX_2003_0401, etc. I have created a file group for years 2003 - 2009 and moved the corresponding quaterly tables into their file group. In doing so I have freed up over 800 GB from the primary file group. I need to reclaim this space. Is my only option to shrink the file? If so is there a process I can do to some how reorganize the data so the shrink would be faster? Currently it is taking over an hour to reclaim a few hundred mb. The hardware I am using is 16 cores and 64 GB of RAM, Windows 2008 with SQL 2005. Any tips, tricks, advice, or warnings are greatly appreciated. The reasoning behind splitting the data into multiple file groups is to aid in backups and purging of data. It is much easier to backup 100 gb than 900 gb since the historical data will never be changing, then when we can delete a year worth of data it is in its own file group.
if i were you i would move everything out to new filegroups, then that way, when you go to shrink the primary file then there will be little for it to do, and it should be quick. plus, having things in the separate filegroups creates the possibility of moving things onto different disks according to usage patterns. you've started on that route, so just carry on down the same path...
