Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DBCC SHRINKFILE EMPTYFILE Expand / Collapse
Author
Message
Posted Tuesday, September 4, 2012 6:45 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, September 7, 2014 6:49 AM
Points: 79, Visits: 217
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.
Post #1354288
Posted Tuesday, September 4, 2012 7:48 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 16, 2014 11:04 PM
Points: 286, Visits: 580
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?
Post #1354295
Posted Tuesday, September 4, 2012 7:57 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, September 7, 2014 6:49 AM
Points: 79, Visits: 217
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
Post #1354297
Posted Tuesday, September 4, 2012 9:21 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 16, 2014 11:04 PM
Points: 286, Visits: 580
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?
Post #1354303
Posted Wednesday, September 5, 2012 7:45 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, September 7, 2014 6:49 AM
Points: 79, Visits: 217
Clare, no worries. I should have been clearer explaining my request and situation. Thank you for chiming in.
Post #1354545
Posted Wednesday, September 5, 2012 1:22 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:18 PM
Points: 2,192, Visits: 3,299
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1354845
Posted Wednesday, January 9, 2013 5:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 15, 2014 10:47 AM
Points: 4, Visits: 32
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.

Post #1405080
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse