When a data file is created in a SQL Server database, the file's autogrowth factor is set by default: Files are set to grow by 10% of their size whenever they're close to running out of free space -- and they are set to grow in an unrestricted fashion. For modestly sized databases (a few megabytes), this isn't a problem. For databases that grow to be gigabytes in size, this growth model can cause problems, which from the outside look like connectivity issues.
Let's assume you have a 10 GB database file that is used aggressively and is set to grow by 10%. Whenever it runs low on room, SQL Server has to lock all operations to that database and add space on the file. Ten percent of 10 GB is 1 GB. It takes a fair amount of time to allocate that much space on a disk, even on a fast system. What makes the percentile growth model even more problematic is that each successive growth will take a little longer -- 10% of 11 GB is 1.1 GB and so on. Think about the growth requirements for a database that has 50 GB or more and you'll quickly see where this leads.
Eventually, the growth periods become so long that the lack of accessibility for the database during that time becomes noticeable. Any attempts to access the database in question are either held up significantly or time out completely. To people who don't know what's really happening on the back end, it will seem like a connection issue. The problem is exacerbated if you have multiple databases on the same physical drive that do this. Even if those databases resize at different times, the larger ones can slow things down terribly.
The most basic way to prevent this from happening is not to use a percentile growth model on databases that are multiple gigabytes in size. I have a number of databases that grow very aggressively on my SQL Server installation, which are eventually trimmed back (the data is exported for archiving). I use the grow-by-megabytes properties for those files rather than grow-by-percentages. For most files, a 1 MB growth allocation should be more than enough, since 1 MB allocates very quickly and keeps the total growth of the file down to a manageable level.
About the author: Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!
More information from SearchSQLServer.com
Fast Guide: Controlling access to SQL Server
Tip: Access SQL Server securely using Windows domain accounts>
Tip: Research best practices for SQL Server access control
