Warning: Autogrow of file 'DB File Name' in database 'DB Name' took <number> milliseconds. Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.
The message on subject is self-explanatory where your disk subsystem is referring to SQL Server that it is unable to cope up the demand.
Having AUTOGROW option enabled on a SQL Server database is a common setup that allows SQL Server automatically expands the database when additional space is required. Internally within the SQL engine this database growth process occurs when the worker thread that is processing a client request determines that the database file or the log file is out of space. The default value for file growth interval is 10 % that is calculated on current size of data or the log file.
On the operating system level when such process is initiated from SQL Server of autogrow operation, the worker thread that performs the autogrow operation may hold on to critical database resources, such as locks that the worker thread previously obtained when it processed the user query. Therefore, concurrency may be negatively affected while the autogrow operation is in progress. Because of this, you may notice blocking, application query time-outs, and other performance-related issues on the database.
So by default the long running process or a cancelled process due to the locks the SQL Server writes a warning message to the error log that indicates the result of the autogrow operation, this may also happen when you have interrupted the auto grow process. Since the Service Pack4 for SQL 2000 initiation the above warning message is logged depending on the outcome of the database autogrow operation. So as per the additional text of the message if this message is logged continuously within the error log then you must consider using FILEGROWTH option to a fixed number value such as 100 mb or smaller percentage value using ALTER DATABASE statement. This will help you to use these warning messages in diagnosing performance problems that occur because of long-running autogrow operations or autogrow operations that are not successful.
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.