February 12, 2013 at 7:01 am
We have an 600gb database as a single mdf and ldf. Due to various reasons, we are planning to split them into multiple files (4) as,
Database_Data.mdf
Database_1.ndf
Database_2.ndf
Database_3.ndf
Database_4.ndf
Database_log.ldf
I have decided on adding new NDF files and moving data into it and keeping each ndf file in a drive.
I was wondering if this is same as creating filegroups and whether filegroups option woud suit me.
February 13, 2013 at 7:52 am
It depends on whether you want to control what goes in each physical file or whether you want to let SQL Server control that. If you add more files into the PRIMARY filegroup then SQL Server will start putting data into those files using its proportional fill algorithm. If you create new filegroups to go along with your new files then you will have to manually move tables or indexes into the new filegroups to get data into the new files.
What are you trying to accomplish with creating more data files?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 13, 2013 at 8:18 am
balasach82 (2/12/2013)
We have an 600gb database as a single mdf and ldf. Due to various reasons, we are planning to split them into multiple files (4) as,Database_Data.mdf
Database_1.ndf
Database_2.ndf
Database_3.ndf
Database_4.ndf
Database_log.ldf
I have decided on adding new NDF files and moving data into it and keeping each ndf file in a drive.
I was wondering if this is same as creating filegroups and whether filegroups option woud suit me.
Creating the files on their own without doing anything else will just put them into the default PRIMARY filegroup.
Create filegroups first and then create the files into the filegroups. You would then need to move objects between the filegroups as already mentioned by OPC
Transaction log files have no concept of filegroups.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 13, 2013 at 12:09 pm
My database files are in 1 drive. My database has grown huge andwill keep on increasing in size. To better utilise the disk space and to manage the db, we areadding new files.
Its clear that i have to create filegroups and add file\files to the new filegroups, then move the data into these filegroups
Am assuming that adding files ot primary group keeps it in a single drive. Is my assumption correct?
February 13, 2013 at 12:12 pm
balasach82 (2/13/2013)
My database files are in 1 drive. My database has grown huge andwill keep on increasing in size. To better utilise the disk space and to manage the db, we areadding new files.Its clear that i have to create filegroups and add file\files to the new filegroups, then move the data into these filegroups
Am assuming that adding files ot primary group keeps it in a single drive. Is my assumption correct?
No. You can have files on different drives participate in the same filegroup. That is actually how I would recommend you moving as a first step. Let SQL Server manage where to store the data based on the proportional fill algorithm. Just make sure your data files are all the same size.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 13, 2013 at 12:29 pm
balasach82 (2/13/2013)
My database files are in 1 drive. My database has grown huge andwill keep on increasing in size. To better utilise the disk space and to manage the db, we areadding new files.Its clear that i have to create filegroups and add file\files to the new filegroups, then move the data into these filegroups
Am assuming that adding files ot primary group keeps it in a single drive. Is my assumption correct?
I'm guessing your database already has objects created and data loaded?
At this point adding files to the primary file group will just destroy the proportional fill algorithm. Quite honestly if you only have 1 drive I wouldn't even bother with the extra files, just extend the primary file.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 13, 2013 at 12:33 pm
Agreed if you only have one drive. I got the impression more drives were available or were being introduced.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 14, 2013 at 5:35 am
Perry, OPC,
now the db is in one drive. We do have 4 drives. My database is already populate, its 523gb now.
1. Can I create new files in new drive but in Primary filegroup
2. since i have 5 drives, am planning to add 5 filegroups, with one NDF file in each filegroup. So, i have to move tables or indexes into new filegroups. correct?
3. Basic Steps to move tables from primary to new filegroups.
February 14, 2013 at 5:57 am
balasach82 (2/14/2013)
Perry, OPC,now the db is in one drive. We do have 4 drives. My database is already populate, its 523gb now.
1. Can I create new files in new drive but in Primary filegroup
2. since i have 5 drives, am planning to add 5 filegroups, with one NDF file in each filegroup. So, i have to move tables or indexes into new filegroups. correct?
3. Basic Steps to move tables from primary to new filegroups.
1 - Yes
2 - Yes
3 - Drop and recreate the clustered index on the new filegroup CREATE INDEX ... WITH DROP_EXISTING
February 14, 2013 at 6:07 am
balasach82 (2/14/2013)
My database is already populate, its 523gb now.1. Can I create new files in new drive but in Primary filegroup
If the database and its primary filegroup is already at the size above then I don't recommend addin new files to the primary filegroup. The proportional fill algorithm is shot at this point
balasach82 (2/14/2013)
2. since i have 5 drives, am planning to add 5 filegroups, with one NDF file in each filegroup. So, i have to move tables or indexes into new filegroups. correct?
Yes, that is correct.
balasach82 (2/14/2013)
3. Basic Steps to move tables from primary to new filegroups.
Do the table objects you are moving have clustered indexes upon them?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 14, 2013 at 6:48 am
Here is good info on how to "rebalance" the data in your data files, and why you should do it that way:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 14, 2013 at 8:13 am
OPC, am reading at the link
Perry,
1. The tables which we need to move doesnt have any index on them. How to move then?
2. Can you please explain which one is better, having new files in primary or having them in secondaryew file groups?
3. What are the pros & cons of having new files in primary or separting them and having it in new filegroups.
am reading files and filegroups in MSDN. It was not clear. So, any help would be good
February 14, 2013 at 8:29 am
balasach82 (2/14/2013)
OPC, am reading at the link
The article I linked to answers #2 and #3 quite well.
To move a heap to a new filegroup create a new heap with a temporary name on the destination filegroup, copy your data to the new heap, drop the old heap and then rename the new heap to have the original name. This gets more complicated when you have nonclustered indexes or foreign key constraints associated with the heap.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 18, 2013 at 11:43 am
If i had to move back a table from a filegorup in diff drive to primary, which is also in diffrnt drive, then what should be done?
February 18, 2013 at 11:46 am
1. If i had to move a table from a filegroup in drive A back to paimary in drive B, what should i do?
2. A table and its clustered index has to be in same filegroup/same drive?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply