dividing database across multiple datafiles

Question

dividing database across multiple datafiles

azad maratha

Default port

Points: 1468
More actions
July 12, 2010 at 9:07 am

#226717

i dont know if its a silly question.but i have a database which consist of one data file.now i want to add additional files which i can.
but is it possible to partition that primary data file across multiple files(mdf + ndf).
for eg.if one mdf is 100 gb.i can add another file.but that 1st file will remain 100gb.can i split that
file.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

If you add another file in a different filegroup, you can chose to move objects to the new filegroup. You have to drop and create the clustered index (or create with drop_existing) and specify the new filegroup.

If the files are in the same filegroup, you can't specify move objects onto it, and SQL will spread new data across all the files in the 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

azad maratha Default port Points: 1468 More actions · Answer 2

but if there are 4 data files and there is only one filegroup (primary).now if there is insertion going on.so the data will be divided between 4 files.i mean how does it work.will it fill the primary then go onto fill secondary files.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

azadsale (7/12/2010)
but if there are 4 data files and there is only one filegroup (primary).now if there is insertion going on.so the data will be divided between 4 files.i mean how does it work.will it fill the primary then go onto fill secondary files.

Yes, you are correct.

Moreover, if data files are set for autogrowth they will start to autogrowth - in a round robin basis - only after all data files on the filegroup are filled up to capacity.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

george sibbald SSC Guru Points: 104210 More actions · Answer 4

azadsale (7/12/2010)
but if there are 4 data files and there is only one filegroup (primary).now if there is insertion going on.so the data will be divided between 4 files.i mean how does it work.will it fill the primary then go onto fill secondary files.

data files in the same filegroup are filled on a round robin, proportional fill basis.

By this I mean the data files are written to in turn but how much data is written depends on how EMPTY they are, so a file with 200MB empty space will write twice as much data as a file with 100MB empty space.

This way the data files become full at about the same time so will autogrow one after the other at about the same time.

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

azad maratha Default port Points: 1468 More actions · Answer 5

is there a article related to (writing data pages to multiple data files)this part.becoz my doubts r still not cleared.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Yes, did you try a search?

To start with:

http://msdn.microsoft.com/en-us/library/ms187087%28SQL.90%29.aspx

http://msdn.microsoft.com/en-us/library/ms191433%28SQL.90%29.aspx

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

azad maratha Default port Points: 1468 More actions · Answer 7

azad maratha

Default port

Points: 1468

July 13, 2010 at 1:31 am

#1191818

thanks