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

最新推荐文章于 2023-02-23 16:17:37 发布

转载最新推荐文章于 2023-02-23 16:17:37 发布 · 1.6k 阅读

Sql Server 专栏收录该内容

4 篇文章

订阅专栏

本文探讨了 SQL Server 数据文件自动增长过程中可能遇到的问题及解决方案。当数据库达到一定规模时，默认的自动增长设置可能导致性能下降。文章建议将自动增长单位从百分比改为固定 MB 数量，或在非高峰时段手动扩大文件大小。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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.