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

Adding Secondary Data Files (ndf) Expand / Collapse
Author
Message
Posted Wednesday, August 29, 2012 12:27 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, July 19, 2014 10:09 PM
Points: 118, Visits: 146
I have a 310GB mdf for my warehouse database. I have just added three more data files to it in order to distribute the data. Now, what do I need to do to ensure data is been written to the new files? What should the size of each new datafile be? Please advice.
Post #1351828
Posted Wednesday, August 29, 2012 12:41 PM


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 @ 1:14 PM
Points: 40,615, Visits: 37,081
If they are in the primary filegroup, new data will be automatically written to them. SQL will not however re-balance existing data. Read up on proportional fill for how the data will be distributed.

As for how big they are, well, how big do they need to be for the future growth of your database?



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 #1351840
Posted Thursday, August 30, 2012 12:46 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 8:55 PM
Points: 1,076, Visits: 3,054

For adding new data files (ndf or mdf) in primary filegroup, application might be gets slow performance due to round robin seraching data from different datafiles. Are you any specific reason for adding new data files?
Post #1352026
Posted Thursday, August 30, 2012 1:48 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 @ 1:14 PM
Points: 40,615, Visits: 37,081
ananda.murugesan (8/30/2012)

For adding new data files (ndf or mdf) in primary filegroup, application might be gets slow performance due to round robin seraching data from different datafiles.


Unlikely to be noticeable unless you have lots of files (lots >>> 4) and the data is on disk not in memory



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 #1352044
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse