Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Moving database files by shrinking with emptyfile

Author  Topic 

stephe40
Posting Yak Master

218 Posts

Posted - 2007-07-13 : 12:00:08
I have a VLDB, ~300 GB. The previous DBA allocated this thing with 26 data files, in 1 file group. However it really does no good because they are all on the same logical drive, in the same folder even. Also the files were not pre allocated, so they are all different sizes, expanded by autogrow, most averaging around 15 GB each.

I want to consolidate this guy to 6 larger data files and have them all on different physical SAN drives. Once I have the drives exposed to the server, can I move the database to these new drives without taking an outage, by adding my 6 new data files to the database, pre-allocating them to the approperiate size, and then start using dbcc shrinkfile with the emptyfile option on all the old data files? Has anyone moved the physical data files of a dabase by doing this? I am not really concerned about performance while the maintenance is done. I just want to avoid an outage.


- Eric

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-13 : 12:26:03
Yes, you can do that, as long as they are in the same filegroup. It will probably take a long time, likely a number of days, but it shouldn't cause any downtime. I have moved data to new files in databases running with heavy transaction processing loads without a problem.

I would allocate the files on the new drives at least 30% larger than the expected final size while you are moving them. You don't want them growing while you are moving data.

Also, after you allocate the new files, shrink each of the original files first so that they have no free space and set them to no autogrow. This will prevent SQL from moving data to them while you are doing the DBCC SHRINKFILE EMPTYFILE. You can use this script for the initial shinks. Set the @TargetFreeMB to say 5 MB.
Shrink DB File by Increment to Target Free Space
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355

Make sure you have transaction log backups running on very short cycles, every 5 minutes or so, and they you have plenty of disk to hold the transaction log backups. You will need a lot.




CODO ERGO SUM
Go to Top of Page

stephe40
Posting Yak Master

218 Posts

Posted - 2007-07-13 : 13:35:09
Awesome. Exactly what I needed to hear. Thanks.

- Eric
Go to Top of Page
   

- Advertisement -