We observed that very often we get information/error at error log something like this:
2011-10-21 12:02:25.54 spid876 Autogrow of file ‘XXX’ in database ‘XXX’ took 73719 milliseconds. Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.
Generally, the database file (data/log) is set to auto grow and growth rate sets to 10%. By default data file is creating with
So with 10% growth will be good for smaller size, what if size become huge of size 50gb or 100gb so 10% of that will be 5gb or 10gb, so to grown up that big size will take quite good time and could also cause performance issue.
>> Change the growth rate of this database from 10% to smaller fixed size (eg 10mb -100mb) depending upon the transactions.
>> Standard practice is if you know your system you could manually maintain the size, and keep it increasing at non peak hrs periodically (with safer side
keep auto-grow and growth rate on)