June 15, 2011 at 4:09 am
The usual recommendation is to keep ~15-20% free space on a drive, but does this only apply to o/s drives? I'm guessing you want to leave room for the paging file, temp files and to allow disk defrags to run effectively etc.
What about drives on a production database server though? For example, if you have a 2TB drive that contains one or more database files that are set not to autogrow. Fragmentation isn't likely to be an issue with large allocations for the database files and the drives won't be used for anything else like paging files etc. Is there a reason for leaving freespace on these drives? Any opinions for or against filling the drives to capacity?
We actually have a number of 2TB volumes used for database files on our SAN and the amount of freespace we are keeping is significant. I'm just wondering if it's actually required or not. We are not in a position where we need to use this storage - I'm just curious...
DBA Dash - Free, open source monitoring for SQL Server
June 15, 2011 at 4:28 am
I cant think of any severe issues with filling drives to capacity esp if they are not going to grow and no index rebuild etc are gonna happen, and the tempdb is not on the same drive. but I know i wouldn't do it esp in a production environment.
I would prefer not to shoot myself in the foot if i can help it 😀
June 15, 2011 at 4:56 am
Jayanth_Kurup (6/15/2011)
I cant think of any severe issues with filling drives to capacity esp if they are not going to grow and no index rebuild etc are gonna happen, and the tempdb is not on the same drive. but I know i wouldn't do it esp in a production environment.I would prefer not to shoot myself in the foot if i can help it 😀
The files certainly wouldn't grow, but there might be index maintenance in some cases. I can't think why index maintenance would be an issue though. e.g. If the file is full and set not to auto grow and we've created a new file on a different drive with free space. Is it going to be any different if we've left freespace on the drive vs filling it to capacity? In both cases there is no space available in the file to use, but there is free space on another file that is part of the same filegroup.
In most cases, the files should be relatively if not completely static and not subject to index maintenance.
I'm guessing it's always good to have extra storage that you can use in an emergency situation. Our growth is normally well forecast and storage is acquired ahead of time.
DBA Dash - Free, open source monitoring for SQL Server
June 15, 2011 at 5:30 am
In a perfect world, 15% is probably workable, but with unexpected data loads, badly performing apps, failed maintenance jobs, sunspots, alien invasion... I prefer to run 30% free, minimum, unless I know that the system is extremely stable.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 15, 2011 at 7:32 am
I prefer to know that I'm going to have at least 2 years worth of growth out of my free space. So I add more than enough to account for the biggest version of tempdb I've seen, plus all extrapolated log file and data file growth.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply