Autogrow of file '******' in database '******' was cancelled by user or timed out after ****** milli

Autogrow of file 'XXXX' in database 'YYYY' was cancelled by user or timed out after ZZZZZ milliseconds

 

Consider the following scenario:

 

-       You have not configured the autogrow of your SQL Server files manually and instead rely on the default values.

-       While the database size is small you don’t face any strange behavior.

-       After the database reaches a big size you get hot by severe performance issues.

-       While checking the SQL ERRORLOG you see this error:

 

Autogrow of file 'XXXX' in database 'YYYY' was cancelled by user or timed out after ZZZZZ milliseconds

 

This happens because by default the autogrow of SQL Server data files is set to 10%. While this value is fine for small database sizes, it is certainly not recommended for large databases.

 

e.g. the 10% autogrow of a data file that is 100 GB is 10 GB

 

If at the time that the autogrow occurs, the workload on the hard disk is big and the autogrow is not successful within 30 seconds, the autogrow will fail. Then as more requests for data file space arrive, SQL Server will try to autogrow the file again and again and these requests will probably fail again after 30 seconds due to heavy workload.

 

So what can you really do in this case? Here are two solutions:

 

1.    Change the Autogrow setting from % to MB. A typical value for the Autogrow of files is e.g. 50 or 100 MB. This will prevent large Autogrow operations which may time out.

 

Or you may:

 

2.    Avoid using Autogrow and proactively (i.e. manually) expand the files to a big enough size during off-peak hours. This way you will also gain a performance benefit since no Autogrow operations will take place while normal SQL operation takes place.