I was reading a few forum posts yesterday where a few people were complaining that the transaction log had grown during index maintenance and they were asking:
- If they should have autogrowth enabled?
- If they should restrict the size of the file?
I think autogrowth should be enabled on all databases but it should only be used as a fall back, or fail safe. The DBA should be sizing the database appropriately right up front and then monitoring the database size and space used to decide if and when to grow it again manually, and always increase size for reasonable medium term use. I think autogrowth is fine as a fail safe in case you miss something but if you rely on it to size your database you will find it kicks in at the most in at appropriate times, which you have no control over, which can affect performance. When setting up autogrowth I like to set the files to grow by fixed amounts in megabytes, and not let the files grow by a percentage of their current size. This is especially important for the log because as the size of the log increases the more megabytes it will grow by increases. 10 percent of 100 is not the same as 10 percent of 200 etc and the size of the growth of the log can effect the size of the Virtual Log Files (VLFs) inside the transaction log. The link takes to the blog of SQL Server MVP Kimberly Tripp (Blog). So to summarise I like to enable authgrowth as a fail safe, set the growth to be fixed amounts of megabytes and monitor database file sizes and set the file size to appropriate values and growth manually when needed.
Regarding the restrict the size of a database file option. Then I generally leave that set to unrestricted because I monitor and have alerts setup on free disk space so I’m able to work on storage before it becomes an issue and I don’t want my database falling over because I set a limit on the size of the file. I’m sure other people have different views on this and I would be more than interested to know if people do things differently and why, in the comments below.
