May 29, 2009 at 4:59 am
Hey,
Database had 1 secondary file in a filegroup as the default. This grew to 50gb. Added 3 new files within the filegroup. I want this 50gb to now spread across the 4 files. Can I get this to do so without too much hassle? So far the 3 new fiels ar ejust sitting on 4gb each.
Cheers,
Shark
May 29, 2009 at 7:33 am
You should have created a new filegroup and move some tables to it.
By moving the clustered indexes to the new filegroup the tables will be moved.And for tables which dont have clustered indexes you can create one on the new filegroup later you can drop the clustered index.
May 29, 2009 at 7:36 am
I don't want a new filegroup I want one filegroup with multiple files. I guess I could now create a new file and filegroup - move the indexes there - and then move them back to the old filegroup to share the 4 files equally. Will work - but pretty long way round. Hoped there would be a quick fix.
May 29, 2009 at 8:08 am
You could also create the number of files you want to end up with and use dbcc shrinkfile with the EMPTYFILE argument on the original file...once the data has been moved from the original file to the other files then the original file can be dropped using ALTER DATABASE....
May 29, 2009 at 8:28 am
If I want the original file to remain in use can I just leave it in and the database will start to use it again? Or will it be marked as "Empty" or something?
Another side question. I have a database (Database A) with Filegroup called "fgDatabaseA". I need to make backups of this database and restore as Database B, C or D. In doing so I would like the filegroup to be "fgDatabaseB". I would also like the logical names of all files to match the new database and not the old. Is this possible?
Thanks loads for the help
May 29, 2009 at 10:27 am
I don't think it will use the file once you use the EMPTYFILE argument...
You can use Alter Database Modify File and/or Modify Filegroup to rename files and filegroups.
To restore Database A to Database B, I would create the new filegroup (and files) first, create Database B on the new filegroup, and use Restore Database 'Database B' FROM 'backup filespec' using the WITH MOVE clause to specify the new files.
See Restore Database in BOL...
May 29, 2009 at 1:28 pm
SQL fills files in a filegroup on a proportional fill basis. depending on how much free space is available. So it will start to spread data between the files without you doing anything more. So if you are looking to do this for performance reasons it works best if the new files start off the same size. Looks neater too.
---------------------------------------------------------------------
May 29, 2009 at 1:45 pm
Michael Belcher (5/29/2009)
I don't think it will use the file once you use the EMPTYFILE argument...
emptyfile just empties the file. To actually stop using it you need to use alter database remove file to get rid of the file. You cannot do this to the primary file,
To restore Database A to Database B, I would create the new filegroup (and files) first, create Database B on the new filegroup, and use Restore Database 'Database B' FROM 'backup filespec' using the WITH MOVE clause to specify the new files.
See Restore Database in BOL...
personally I would restore databaseB from the databaseA backup using the with move options. Then rename the filesgroups using alter database modify filegroup name clause and if you want to do the files the modify file name clause
see ]http://msdn.microsoft.com/en-us/library/bb522469.aspx
---------------------------------------------------------------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply