Article ID: 2091024 - View products that this article applies to.
SQL Server will report messages to the Application Event Log and ERRORLOG when an autogrow of a database and/or transaction log file has timed out or has taken a long period of time to complete:
Autogrow of file '<file>' in database '<database>' was cancelled by user or timed out after <n> milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.
A long autogrow of a database and/or transaction log file may cause performance problems. This is becasue an operation that requires the autogrowth of a file will hold on to resources such as locks or latches during the duration of the file grow operation. You may see long waits on latches for allocation pages. The operation that requires the long autogrow will show a wait type of PREEMPTIVE_OS_WRITEFILEGATHER.
Autogrow of file '<file>' in database '<database>' took <n> milliseconds. Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.
A long autogrowth of a file is usually caused because a file is configured for a FILEGROWTH option for a large fixed value or for a % value and the current file is very large. Autogrowth is only needed if no space is available in the file so this operation is also the sign of low space available within a database or transaction log file. The reason an autogrow operation can take a long period of time is that SQL Server must "zero" the extra space required for the file. This may not be required for database files. See the Resolution section for more detals.
It is not recommended to rely on autogrowth of files as standard operation for obtaining more space in a file. Rather, you should plan out the maximum size of your database and/or transaction log file and allow autogrowth to occur only in unplanned situations for space.
Autogrow of database files may not require a "zero" of the extra space if SQL Server uses a technique called instant file initialization. SQL Server can use this feature on Windows XP or Windows Server 2003 or later operating systems if the SQL Server Service Account has the SE_MANAGE_VOLUME user right. For more information see the topic Database File Initialization
(http://msdn.microsoft.com/en-us/library/ms175935.aspx)in the SQL Server Books Online.
For more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product, see the following table:
Collapse this tableExpand this table
Article ID: 2091024 - Last Review: April 2, 2012 - Revision: 2.0
Contact us for more help