July 2, 2012 at 12:16 am
Hi,
My one of filegroup has reached more than 85% of space and the data files having auto growth is none.
could you please give suggestions on this..
July 2, 2012 at 1:56 am
enable auto growth
manually add space to the file
delete data
July 2, 2012 at 6:08 am
Yes, but it is in production enviornment. So is there any alternative...
July 2, 2012 at 6:21 am
The alternative is to stop adding data to the file.
Jared
CE - Microsoft
July 2, 2012 at 8:15 am
add another file to the filegroup that way SQL will stripe the data across both files in an equal proportion
July 2, 2012 at 8:30 am
anthony.green (7/2/2012)
add another file to the filegroup that way SQL will stripe the data across both files in an equal proportion
No, it won't, as the remaining files in the group are 85% full so they cannot be proportionately filled, This is only relevant when all files are created at the same time. Increase the growth of the existing files would be the better option.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 2, 2012 at 8:34 am
Perry Whittle (7/2/2012)
anthony.green (7/2/2012)
add another file to the filegroup that way SQL will stripe the data across both files in an equal proportionNo, it won't, as the remaining files in the group are 85% full so they cannot be proportionately filled, This is only relevant when all files are created at the same time. Increase the growth of the existing files would be the better option.
I thought it did, so if File1 has 100mb free and File2 has 200MB free then 33.3% of the data is written to File1 and 66.6% is written to File2?
http://msdn.microsoft.com/en-us/library/ms187087%28v=sql.105%29.aspx
Filegroups use a proportional fill strategy across all the files within each filegroup. As data is written to the filegroup, the SQL Server Database Engine writes an amount proportional to the free space in the file to each file within the filegroup, instead of writing all the data to the first file until full. It then writes to the next file. For example, if file f1 has 100 MB free and file f2 has 200 MB free, one extent is allocated from file f1, two extents from file f2, and so on. In this way, both files become full at about the same time, and simple striping is achieved.
July 2, 2012 at 8:52 am
You can add another file, which will help spread the load, but you will continue to add some data to all files.
You can grow the size of the files manually, which is a low impact operation in production.
July 2, 2012 at 10:46 am
anthony.green (7/2/2012)
Perry Whittle (7/2/2012)
anthony.green (7/2/2012)
add another file to the filegroup that way SQL will stripe the data across both files in an equal proportionNo, it won't, as the remaining files in the group are 85% full so they cannot be proportionately filled, This is only relevant when all files are created at the same time. Increase the growth of the existing files would be the better option.
I thought it did, so if File1 has 100mb free and File2 has 200MB free then 33.3% of the data is written to File1 and 66.6% is written to File2?
http://msdn.microsoft.com/en-us/library/ms187087%28v=sql.105%29.aspx
Filegroups use a proportional fill strategy across all the files within each filegroup. As data is written to the filegroup, the SQL Server Database Engine writes an amount proportional to the free space in the file to each file within the filegroup, instead of writing all the data to the first file until full. It then writes to the next file. For example, if file f1 has 100 MB free and file f2 has 200 MB free, one extent is allocated from file f1, two extents from file f2, and so on. In this way, both files become full at about the same time, and simple striping is achieved.
Sorry, maybe i didn't make myself clear, yes it will to a point but as the filegroup is already 85% full its going to write\allocate a hell of a lot more extents in the empty file than any of the others. At this point the proportionate fill is pretty useless. If the filegroup was say 25% or 50% full you may have a better fill rate, even then i wouldn't recommend it. All you're going to do in this case is end up with I\O directed at one file to fill it up. Best performance, and the Microsoft recommendation, is to create the files equal size and growth from the outset, not sometime down the line when some of the files are already 85% full.
It would be much better and easier to manage to just grow the existing files would it not.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 3, 2012 at 2:18 am
Ah equal proportional fill wasnt the best term to use, my mistake on that.
Yes I would in the first instance add more space, but as the OP wanted other alternatives to the 3 I gave at the start, this was the only other one I could think of.
July 3, 2012 at 6:56 am
sure, as i said you only want to be creating multiple files per filegroup from the outset to ensure the fill is equal, (hence sames sizes and growth rates). If you add another file sometime down the line, at some point more or even all of the I\O will be hitting one file when the others fill. I\O patterns will no longer be spread evenly and this will then defeat the object of mutliple files in the first place.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 3, 2012 at 11:33 am
Except that if you are space bound, you can move new data to the new file this way. And if space is your issue, not performance, this can work.
Depending on your load or access pattern (new v "old" data), you may not care. The vast majority of servers I've managed were one file/one file group. Performance was rarely the issue. A few that had multiple files/filegroups had them because of space issues on the drives/arrays we used. Some didn't, but it's not a clear cut issue to not just add a file to the filegroup.
July 3, 2012 at 11:44 am
yes but data is moved to a filegroup, not an individual file. If that group only contains 1 file then fine otherwise it'll be spread across all files in the filegroup.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply