<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 - General
»
Split VLDB into Multiple data files
16 posts, Page 1 of 2
1
2
»»
Split VLDB into Multiple data files
Rate Topic
Display Mode
Topic Options
Author
Message
balasach82
balasach82
Posted Tuesday, February 12, 2013 7:01 AM
Old Hand
Group: General Forum Members
Last Login: Monday, May 13, 2013 8:55 AM
Points: 307,
Visits: 743
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.
Post #1418940
opc.three
opc.three
Posted Wednesday, February 13, 2013 7:52 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 6,715,
Visits: 11,751
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
Believe you can and you're halfway there.
--Theodore Roosevelt
Everything Should Be Made as Simple as Possible, But Not Simpler
--Albert Einstein
The significant problems we face cannot be solved at the same level of thinking we were at when we created them.
--Albert Einstein
1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples.
--Giordy
Post #1419533
Perry Whittle
Perry Whittle
Posted Wednesday, February 13, 2013 8:18 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 10:29 AM
Points: 5,201,
Visits: 11,154
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"
Post #1419551
balasach82
balasach82
Posted Wednesday, February 13, 2013 12:09 PM
Old Hand
Group: General Forum Members
Last Login: Monday, May 13, 2013 8:55 AM
Points: 307,
Visits: 743
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?
Post #1419679
opc.three
opc.three
Posted Wednesday, February 13, 2013 12:12 PM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 6,715,
Visits: 11,751
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
Believe you can and you're halfway there.
--Theodore Roosevelt
Everything Should Be Made as Simple as Possible, But Not Simpler
--Albert Einstein
The significant problems we face cannot be solved at the same level of thinking we were at when we created them.
--Albert Einstein
1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples.
--Giordy
Post #1419682
Perry Whittle
Perry Whittle
Posted Wednesday, February 13, 2013 12:29 PM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 10:29 AM
Points: 5,201,
Visits: 11,154
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"
Post #1419697
opc.three
opc.three
Posted Wednesday, February 13, 2013 12:33 PM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 6,715,
Visits: 11,751
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
Believe you can and you're halfway there.
--Theodore Roosevelt
Everything Should Be Made as Simple as Possible, But Not Simpler
--Albert Einstein
The significant problems we face cannot be solved at the same level of thinking we were at when we created them.
--Albert Einstein
1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples.
--Giordy
Post #1419702
balasach82
balasach82
Posted Thursday, February 14, 2013 5:35 AM
Old Hand
Group: General Forum Members
Last Login: Monday, May 13, 2013 8:55 AM
Points: 307,
Visits: 743
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.
Post #1419999
anthony.green
anthony.green
Posted Thursday, February 14, 2013 5:57 AM
SSCertifiable
Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075,
Visits: 4,831
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
Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1
&
Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger
Post #1420010
Perry Whittle
Perry Whittle
Posted Thursday, February 14, 2013 6:07 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 10:29 AM
Points: 5,201,
Visits: 11,154
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"
Post #1420018
« Prev Topic
|
Next Topic »
16 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
