Adding Secondary Data Files (ndf)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Adding Secondary Data Files (ndf)

Adding Secondary Data Files (ndf)

Rate Topic

Display Mode

Topic Options

Author

Message

soccer

Posted Wednesday, August 29, 2012 12:27 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 12:51 PM
Points: 114, Visits: 97

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

GilaMonster

Posted Wednesday, August 29, 2012 12:41 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 37,687, Visits: 29,946

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

ananda.murugesan

Posted Thursday, August 30, 2012 12:46 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:41 PM
Points: 821, Visits: 2,402

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

GilaMonster

Posted Thursday, August 30, 2012 1:48 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 37,687, Visits: 29,946

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 »

Permissions