How to combine file group

Question

Post reply

How to combine file group

Louis_See1

Ten Centuries

Points: 1100
More actions
December 17, 2002 at 7:59 pm

#64755

I have created a few files group for NDF file group and LDF file group because of not enough harddisk space. After upgraded the harddisk, I will like to combine back all the file group into one MDF file and one LDF file. Did anyone done this before?
Thanks in advance

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

bluestn SSC Rookie Points: 32 More actions · Answer 1

First, move all the objects back to your mdf file group.

Then, use

"ALTER DATABASE REMOVE FILE "

"ALTER DATABASE REMOVE FILE GROUP"

to drop the specified file & the fille group.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

You can set the mdf as the default and recompile stored procedure and views to move them.

For tables, create a clustered index on the mdf.

Steve Jones

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

Louis_See1 Ten Centuries Points: 1100 More actions · Answer 3

Hi, I have too many table's in the NDF file. Is there a faster way to move all the table from NDF to MDF file group?

bluestn SSC Rookie Points: 32 More actions · Answer 4

quote:
Hi, I have too many table's in the NDF file. Is there a faster way to move all the table from NDF to MDF file group?

You can find the tables which are located in the ndf files and put them into the cursor, and then , u can use sp_msforeachtable to do it automatically to move them to your mdf file.

Antares686 SSC Guru Points: 125444 More actions · Answer 5

ALso, are you seperating tables into individual filegroups or is it one table spanning multiple filegroups. If the later you just have to run DBCC SHRINKFILE with the EMPTYFILE option to push all the data from each file but I would fix each NDF so they cannot grow anymore and only the MDF wil accept the data. You can save yourself time by build your command using the sysfiles table liks so.

select 'DBCC SHRINKFILE (''' + RTRIM([name]) + ''', EMPTYFILE)' from sysfiles where [filename] like '%.ndf%'

You can also build the entire script of dropping the file with ALTER DATABASE REMOVE FILE NAME = '' and even ALTER DATABASE MODIFY FILE NAME='' FILEGROWTH = 0. Then you run the script you build this way.

If the other as long as you don't have the text/image data moved from the primary filegroup or the indexes (other then the clustered), you could script to ALTER the table to drop the clustered index and then build then clustered index on the primary filegorup. This will in effect move the table and data back. Again thou if you set the text/image or non-clustered indexes to another location the best method I have seen is EM to deal with that, maybe a DMO solution could do but I have not tried.