April 29, 2014 at 7:37 am
We have a policy to try and keep 20% free space in all database files - especially data and index files. I understand that auto grow is available. But, have heard pros and cons to using it. I have been manually growing my files by XXX GB whenever they get below 20% free space. Recently, I noticed that as soon as I have done a manual grow, the database seems to use up the free space faster than if I let the files sit at less than 20% free. For instance, I had data file that was saying there was only 9% free, so I manually grew the file so there was 21% free. Within a couple of days it was back down to 9%. So, I grew it again to 21%. Within a couple of days it was back down to 9%. So, I left it at 9% to see how long it would take it to get to 0% and do an auto grow. Now it has been a few weeks and I am still waiting. The data file free space is hovering around 8%. We de-fragment indexes weekly. There were no extra inserts going on during the time frame where I saw the increased decrease in free space. Any clues or unwritten rules I should be thinking about?
April 29, 2014 at 8:02 am
Not really. It sounds like either data growth, or space used through fragmentation. Do you have a defrag running occasionally to ensure optimal use of the space? It sounds like you just hit growth spurts in association with your free space to me. I've never seen any other explanation for what you're describing. I've never noticed a crazy growth pattern just because I grew the file sizes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply