SQL Server Performance Forum – Threads Archive
What determines when a file autogrows?What’s the logic that SQL Server 7.0/2000 uses to determine when it’s time to autogrow a file? If for instance I have a database that has a 2MB MDF and a 1MB LDF on primary and I have grow by 10% set, is there a mathematical calc that say’s oh yeah, there’s only 1% free space left, time to grow the file. Or is it more like, this insert/update is not going to fit, time to autogrow. We just want to determine a way to report when SQL is approaching that point. thx, -Rob
The file will grow when there is not enough free space left on a page to perform the insert required. At this point, sql server will autogrow the file (assuming thats how the file is configured), allocate extra empty pages according to the grow factor, and continue in its operations. Check books online for sp_spaceused, to help monitor the size. Also you might like to run a profile and monitor autogrow events to gain a better idea of their frequency on your database.
If the sizes of 2MB and 1MB are accurate to what youre working on, the overhead of autogrow will be minimal. However, once databases get large, the overhead while the file autogrows can sometimes become significant.
>> The file will grow when there is not enough free space left on a page to perform the insert required. Just to clarify, this is better phrased as The file will grow when there is not enough free space to allocate a new page when required (since each page does not necessarily have to be full, according to indices and fill factor etc).
For more details in this topic refer to Inside SQL server 2000 by Kalen Delaney.