Blog Post

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.

Read 741 times
(2 in last 30 days)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating