SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Moving database files by shrinking with emptyfile
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stephe40
Posting Yak Master

218 Posts

Posted - 07/13/2007 :  12:00:08  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 07/13/2007 :  12:26:03  Show Profile  Reply with Quote
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 - 07/13/2007 :  13:35:09  Show Profile  Reply with Quote
Awesome. Exactly what I needed to hear. Thanks.

- Eric
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000