September 4, 2012 at 6:45 pm
Hello, I have a 1TB datafile that I need to move onto several files to lessen it's size. Has anyone successfully accomplished this with such a large file? If so, how long may I expect it to take? Please provide any experiences you have had with this process. Thank you.
September 4, 2012 at 7:48 pm
Hmm..firstly I don't understand what the title of your post has to do with your question.
However, if you have a single mdf file, then that file is your primary datafile and you can't remove it. In order to decrease the size of this MDF file you will first need to:
Create a new datafile on a different physical disk
Create a new filegroup
Add the datafile to the newly created filegroup
Next you need to physically move some of your data from the the primary MDF file into your newly created datafile. There are a number of ways to choose which objects to move into this new datafile but, once you have your plan in place, essentially the easiest way to migrate a table to the new datafile, is to rebuild its clustered index on the newly created filegroup.
To answer your question about time, this step will take as long as it usually takes to rebuild the clustered index on the tables you plan to move. If you have a 1TB database I expect you will have some substantial tables.
Once you have migrated sufficient data to allow you to reduce your MDF to its required size, you can then use the DBCC SHRINKFILE command to shrink the MDF to its 'new' size. You will need to rebuild the indexes on this primary filegroup, once the shrink has completed as the shrink operation will fragment the data. Shrinking a datafile this size probably take several hours and is a task best scheduled out of hours.
At this stage you have two smaller datafiles and subsequent object creation using the ON <FILEGROUP> syntax (or by changing the default filegroup for the database) will give you control over the placement of objects.
September 4, 2012 at 7:57 pm
I am merely trying to get feedback on using the DBCC SHRINKFILE EMPTYFILE command, hence the title of my post. I am not referring to my PRIMARY file. I have a 1TB datafile that needs to be moved to newly created datafile in the same filegroup. Due to application limitations, there can only be the one existing filegroup, so I only have the choice of adding more files. I hope this explains it better. Any input is greatly appreciated.
Thank you
September 4, 2012 at 9:21 pm
oradbguru (9/4/2012)
I am merely trying to get feedback on using the DBCC SHRINKFILE EMPTYFILE command, hence the title of my post. I am not referring to my PRIMARY file. I have a 1TB datafile that needs to be moved to newly created datafile in the same filegroup. Due to application limitations, there can only be the one existing filegroup, so I only have the choice of adding more files. I hope this explains it better. Any input is greatly appreciated.Thank you
Apologies for my misunderstanding. No experience in this scenario I'm afraid..pesky app developers huh?;-)
September 5, 2012 at 7:45 am
Clare, no worries. I should have been clearer explaining my request and situation. Thank you for chiming in.
September 5, 2012 at 1:22 pm
oradbguru (9/4/2012)Due to application limitations, there can only be the one existing filegroup.
Thank you
?? Not sure I follow. Applications should be completely unaware of any filegroups in SQL Server.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 9, 2013 at 5:03 pm
Run this before running DBCC Shrinkfile/Emptyfile, open a SSMS window:
select getdate()
dbcc showfilestats
This will give you a baseline of how many extents (8 x 8Kb pages) there are in each file storing table data. Total extents correlates to the total file size (# extents x 640Kb). Used extents shows how many extents are actively being used to stored data. DBCC Shrinkfile/Emptyfile will move each extent and spread it to one of the other data files.
As the shrinkfile progresses, the used extent count will fall. Since Shrinkfile/Emptyfile assures you that no new data will be added to the file, you are then waiting for this value to reach zero.
After a period of time, open another SSMS window and run the above command again. Then you can simply calculate the differences of the extents used and the elapsed minutes to get a extents/min rate. Take the number of extents left and divide by the rate and that will give you an accurate estimate of the time remaining.
Hope this helps.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply