SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multiple Data Files


Multiple Data Files

Author
Message
shindle 17293
shindle 17293
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 653
I have a db that has 3 data files. The first data file is on one drive and the remaining two on another. All three were set to autogrow with no limit on maxsize.

My understanding is that data is spread evenly across all three data files? So as the database grows data is placed evenly across all three data files and each file would autogrow as required until maxsize is reached. However, I noticed that the third file had not really grown which surprised me? The first two were set to the same initial size but the third was set to a much smaller size - would this have caused that?

Also, should there not be a limit set on the max size of each file to avoid filling a drive, particularly on the drive with two data files?

Thanks

Steve
GilaMonster
GilaMonster
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: General Forum Members
Points: 224498 Visits: 46317
Are they all in the same filegroup?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54067 Visits: 17681
shindle 17293 (11/14/2013)
I have a db that has 3 data files. The first data file is on one drive and the remaining two on another. All three were set to autogrow with no limit on maxsize.

My understanding is that data is spread evenly across all three data files? So as the database grows data is placed evenly across all three data files and each file would autogrow as required until maxsize is reached. However, I noticed that the third file had not really grown which surprised me? The first two were set to the same initial size but the third was set to a much smaller size - would this have caused that?

Also, should there not be a limit set on the max size of each file to avoid filling a drive, particularly on the drive with two data files?

Thanks

Steve

Can you post results from the following query please

select d.name, f.name from sys.database_files d
inner join sys.filegroups f on d.data_space_id = f.data_space_id



-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
shindle 17293
shindle 17293
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 653
Yes they are all in same filegroup.

After doing some more research I discovered that the data files are filled by the same amount if each file is the same size (round robin fashion) which can improve performance. However, if one or more of the files is larger than the other(s) then this will be filled by a larger % than the smaller one(s).

In terms of the autogrow feature each file will just grow as per the setting specified when required.

The maxsize is also a setting that doesn't really impact this process - when the maxsize is reached that file will not grow but the others will continue to if space is available.

Please let me know if there is anything I've misunderstood.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search