June 30, 2009 at 8:21 am
I have a 650g database on a 750g drive. I am asking the Storage team for more space. They say they cannot expand the drive but can only swap it out with a bigger drive. So I asked if I could just have another new drive instead.
the question is, if I just add a new file to the database (.ndf) on this new drive, will SQL Server automatically proportionally fill the new .ndf file? Even if it's a different size from the primary?
Will I need to turn off auto-growth?
thanks very very,
Tim White
July 2, 2009 at 3:05 am
After all the practicals i found that if you create a new NDF with same zide as of old MDF and both the files are in same filegroup it will follow the proportional fill algorithm .
If the size of the new file is less than the old one then also it will try to fill both the files but will not be able to keep the sizes same .E.g if two files are 4 and 6 MB respectively ....it will keep on filling them together and you might finr them at some point as 45 MB and 44 MB.
If the difference is very high , still it will fill both .But it will not wait to fill NDF to 260 GB and then continue with proportional fill ..
But it will still try to fill the gap ..
for example initially I created 2 files with the ratio of 3.33 : 1
later it was in the ration of 1.07 is to 1 and in some time 1.05 : 1..
So i realized the larger the ratio the more it will take to reduce it .but it will try .
Now if you try it with 650 GB Vs 100 or 50 GB it will take some good time to bring it to 1:1 <-- i.e. proportional fill ..
Regards
Abhay
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
July 2, 2009 at 6:56 am
Thanks Abhay, really appreciate it. The books speak of proportional fill in cloudy terms and I was looking for someone who actually had experience with it.
What about the auto-growth flag? on/off? Does it matter as far as proportional fill goes? One file? Both Files?
Thanks again
Tim White
July 2, 2009 at 8:01 pm
If you keep the auto-growth on then engine will follow the proportional fill algorithm .However the moment one of the file hits its limit then it will use the other file which might have its autogrow not restricted (instead of throwing 9001 or 9002 error for the first file).
Note : a good practice is to set the auto grow to 512/1024 MB and not in percentage.
Regards
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
July 3, 2009 at 6:24 am
The proportional fill algorithm is a lot more simple than maybe some people think.
When SQL Server wants to add an extent of space to an object the 'proportional fill' algorithm will choose the data file that has the most free space.
This means that if you start with 1 data file and then add a second, all new space requests will get satisfied from file 2, until file 2 has the same amount of free space as file 1. From then on, SQL Server will alternate which file is used until one of them has to grow.
After the file has grown, SQL Server will use that file for all new space requests until the free space is reduced to that in the second file. Then it will use both files again until another growth event occurrs.
In the example above, when you add file 2 to your database, all of the data that exists in file 1 will stay exactly where it is until you do something to move it. The normal way to redistribute existing data is to rebuild the cluster index.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
July 4, 2009 at 1:40 am
I disagree with you Ed (apologize in advance).
As per you if file 1 is 100 MB and file 2 is 50 MB [the ratio is 2:1], file 2 will grow till it reaches 100 MB and then both will fill alternately .
This is incorrect .You can do the practicals and it will be clear to you .
What will happen is :
Both file 1 and 2 will be filled together but file 2 will be filled more than file 1 and during the course of time the engine code will try its best to reduce this ratio to 1:1 .But this will take time .
As i said the bigger is the gap between 'n' files the longer it takes to get it reduced to 1:1 .
But saying that one file will wait for other till it comes at same size is incorrect .
you can use sp_helpdb 'dbname' to test it .
Regards
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply