July 12, 2010 at 9:07 am
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.
July 12, 2010 at 9:29 am
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
July 12, 2010 at 1:00 pm
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.
July 12, 2010 at 1:31 pm
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.July 12, 2010 at 2:50 pm
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.
---------------------------------------------------------------------
July 12, 2010 at 10:48 pm
is there a article related to (writing data pages to multiple data files)this part.becoz my doubts r still not cleared.
July 13, 2010 at 1:18 am
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
July 13, 2010 at 1:31 am
thanks
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply