Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DBCC SHRINKFILE EMPTYFILE


DBCC SHRINKFILE EMPTYFILE

Author
Message
oradbguru
oradbguru
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 289
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.
MissTippsInOz
MissTippsInOz
SSC Veteran
SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)

Group: General Forum Members
Points: 289 Visits: 597
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.

Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
oradbguru
oradbguru
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 289
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
MissTippsInOz
MissTippsInOz
SSC Veteran
SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)

Group: General Forum Members
Points: 289 Visits: 597
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?;-)

Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
oradbguru
oradbguru
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 289
Clare, no worries. I should have been clearer explaining my request and situation. Thank you for chiming in.
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3937 Visits: 6663
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)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
foo-323656
foo-323656
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 33
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search