SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Server - ALTER DATABASE to set a smaller FILEGROWTH for this file.

This error message can appear in the SQL Server Error Log files .

Autogrow of file 'MY_DB' in database 'MY_DB' took 455320 milliseconds.  Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.


What does this mean ?


1)       Need to analyse how you are managing the SQL Server database files sizes

2)       The database files are set at autogrow . Generally , I’d say autogrow should never happen. But , in reality, autogrow does occur –  multiuser modelling environments are an example.

3)       Monitor free space in the database files , and grow manually.

4)       Where autogrowth is required  , use the ALTER DATABASE command , modify the files from a Percentage Growth to to fixed size . This assists in preventing timeouts. Be careful , not to set them to low - as this can cause may VLFs -check this post for details: Database autogrow and slow database recovery

5)       Check Instant File Initialization is set

6)       Leaving the database files at 10% growth , becomes more expensive the large the database file.


Leave a comment on the original post [www.sqlserver-dba.com, opens in a new window]

Loading comments...