Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Multiple Data Files Expand / Collapse
Author
Message
Posted Thursday, November 14, 2013 1:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 10:16 PM
Points: 32, Visits: 375
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
Post #1514162
Posted Thursday, November 14, 2013 1:38 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:55 AM
Points: 40,427, Visits: 36,877
Are they all in the same filegroup?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1514166
Posted Thursday, November 14, 2013 9:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:12 AM
Points: 6,634, Visits: 14,212
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"
Post #1514346
Posted Thursday, November 14, 2013 4:06 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 10:16 PM
Points: 32, Visits: 375
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.
Post #1514520
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse