Microsoft® SQL Server 2012 Unleashed

Managing File Growth

As discussed earlier in this chapter, SQL Server manages file growth by automatically growing files by preset intervals when a need for additional space arises. However, this is a very loose definition of the word manages. What actually happens is that when the database runs out of space, it suspends all update activity, checks whether it is allowed additional space, and if space is available, it increases the file size by the value defined by FILEGROWTH. When the database fills up again, the whole process starts over.

When all the files in a filegroup are full and are configured to autogrow, SQL Server automatically expands one file at a time in a round-robin fashion to accommodate more data. For example, if a filegroup ...