SQL Server reports Msg 5144 and 5145 for long or failed autogrow of databases and transaction log files

Article translations Article translations
Close Close
Article ID: 2091024 - View products that this article applies to.
Expand all | Collapse all

SYMPTOMS

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:

Msg 5144
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.

Msg 5145
Autogrow of file '<file>' in database '<database>' took <n> milliseconds. Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.

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.

CAUSE

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.

RESOLUTION

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 in the SQL Server Books Online.

MORE INFORMATION

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
Rule software

Rule title

Rule description

Product versions against which the rule is evaluated
SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA)





Autogrow Failed or took a long time






The SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA) provides a rule to detect if Events 5144 or 5145 are found in the Windows Application Event Log. The SQL Server 2008 R2 BPA supports both SQL Server 2008 and SQL Server 2008 R2.

If you run the BPA tool and encounter an Error with the title of Engine -Autogrow Failed or took a long time, then Event 5144 or 5145 for the SQL Server instance are found in the Windows Application Event Log. BPA does not filter on any date/time so it will report the number of occurrences of these events in your current Event Log.
SQL Server 2008
SQL Server 2008 R2







SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA)




Autogrow Failed or took a long time





The SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA) provides a rule to detect if Events 5144 or 5145 are found in the Windows Application Event Log.

If you run the BPA tool and encounter an Error with the title of Engine -Autogrow Failed or took a long time, then Event 5144 or 5145 for the SQL Server instance are found in the Windows Application Event Log. BPA does not filter on any date/time so it will report the number of occurrences of these events in your current Event Log.
SQL Server 2012








Properties

Article ID: 2091024 - Last Review: April 2, 2012 - Revision: 2.0
APPLIES TO
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Enterprise X64 Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Standard X64 Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Express
  • Microsoft SQL Server 2008 Express with Advanced Services
  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Express
  • Microsoft SQL Server 2008 R2 Express with Advanced Services
Keywords: 
KB2091024

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com