May 17, 2006 at 2:01 pm
I have a 175 Gig database. We're switching to new storage soon, and I'd like to spread the database over multiple files (and drives). I know I can add new files, limit File1.mdf to 175 so that it doesn't grow beyond 175 G, and then as the DB grows, File2.ndf gets the additional data.
Given that the current DB has just 1 file of 175 G, is there a way to set up the DB on the new storage so that File1.mdf is 100 G, File2.ndf is 75 G with max of 100 G, and File3.ndf is 0 Gig until File 2 is full ?
May 18, 2006 at 3:50 am
what you're asking can be done, but not how you're asking - if that makes sense.
You can split data between filegroups by setting secondary filegroups and designating which tables/data/indexes live on which filegroup.
You can designate multiple files for a database ( NOT filegroups ) and in this case the data will spread its self across the files in a round robin fashion - only problem is that you can ( asfaik ) only do this when you create a new database.
So .. you'd have to port the data to your new multi file database.
For optimum performance you'd create multiple filegroups with multiple files in each.
There are some ms kb articles about this this if you search technet.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 18, 2006 at 1:21 pm
Colin, I believe that you can have multiple 'filegroups' within a database. By default you get a PRIMARY filegroup only. Within a filegroup you can then have multiple files. When you have multiple files within a filegroup the data stored is distributed between the files within the filegroup. This distribution (striping so to speak) occurs on an 'extent' level - 64kb chunks.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
May 18, 2006 at 3:04 pm
sorry I didn't explain myself very clearly - what I meant to say is that adding additional files to an existing filegroup where the database currently has one datafile won't give you the round robin file distribution ( for the data ). If you create a database this way the data will spread across the files ( you have to make all the files the same size with the same growth factors ) , so for an existing database the best way would be to create seperate filegroups and manually decide which data exists in each filegroup. ( I guess you could create your second filegroup with multiple files then you'd get round robin data spread )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 18, 2006 at 3:27 pm
We are in agreement Colin. Thanks for the clarification. As for my usage of this type of database storage construct, I leave all system objects on the 'primary' filegroup and give it a fixed size and 50% growth one time. As for all other filegroups and files, it's a deisgn question best based on the capacity (throughput) of the SAN. One final note as well. Since only system objects reside on the 'primary' filegroup I usually change the 'default' filegroup to a kind of 'garbage' bin as well (minimal allocation and no growth) - it helps catch those developers with sloppy scripts (trash) !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
June 8, 2006 at 2:11 pm
DBCC SHRINKFILE with the EMPTYFILE sounds like what I want, but it's not working. I created additional files, both in the PRIMARY filegroup and SECONDARY. I ran the command, and it completed, but there was no difference in the database. What did I miss ?
June 9, 2006 at 3:42 am
shrinkfile will only attempt to remove unused space from the datafiles .. you need a certain amount of free space in your data files for growth and work space for dbcc commands, re-index etc. Note that using shrinkfile will increase the amount of ntfs fragmentation you get which will impair performance further.
I find I was wrong about the multiple files within an existing database ( well at least with 2000 Ent sp4 ) adding extra files to the filegroup will spread data across the files in a round robin fashion .. have to figure out why a couple I have don't < grin >
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply