Fix SQL Server Msg 5144 and 5145 for long or failed autogrow of data and log files

Whenever you extend the size of your data or log file with a larger value or your database perform autogrow operation to extend its database files with bigger value then SQL Server takes longer time to perform such activities. SQL Server reports these messages 5144 and 5145 in to the Application Event Log and ERRORLOG in case autogrow of a database and/or transaction log file has timed out or has taken a long period of time to complete. The details about such logs are given below:

Msg 5144
Autogrow of file ‘Data or Log file name’ 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.

Or you will get below message if autogrow operation or data/log file size increase operation will run for longer duration than expected.

Msg 5145
Autogrow of file ‘Data or Log file name’ in database ‘Database’ took <n> milliseconds. Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.

Whenever data or log file growth operation is taking lot of time then a wait type PREEMPTIVE_OS_WRITEFILEGATHER will be shown in to SQL Server. If you want to read more about this wait type then click at the attached link. 

Root Cause

Data and log file initialization takes lot of time if you have to create large database files or large data or log file growth will be performed. Whenever we allocate or extend space to data or log file, it first fills with zeros. Zeroing operation takes lot of time for bigger size autogrowth operations that causes for longer run to complete. We generally see these messages (5144 or 5145) during either extending the data or transaction log files or during autogrowth operation for data or log files.

fix msg 5144 and 5145

Microsoft has given a solution named Instant File Initialization to fix the time consuming file size allocation for data files. Once Instant File Initialization will be enabled, your data files size allocation will be done in faster way but unfortunately Instant File Initialization does not work on transaction log files.  Instant file initialization reclaims used disk space without filling that space with zeros. Instead, disk content is overwritten as new data is written to the files. Read below section to overcome these error messages.

Solution

To avoid these messages 5144 or 5145 to be logged, we should plan file size growth carefully. We should set maximum size of the database so that it could not perform frequent autogrowth operation. We can enable Instant File Initialization feature to speed up the data file size allocation. 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. Follow below points to avoid this error.

I hope you like this article. Please follow our Facebook page and Twitter handle to get latest updates.

Related Articles:

Follow me:

Manvendra Deo Singh

I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.
Manvendra Deo Singh
Follow me:
Summary
Fix SQL Server Msg 5144 and 5145 for long or failed autogrow of data and log files
Article Name
Fix SQL Server Msg 5144 and 5145 for long or failed autogrow of data and log files
Description
Whenever you extend the size of your data or log file with a larger value or your database perform autogrow operation to extend its database files with bigger value then SQL Server takes longer time to perform such activities. SQL Server reports these messages 5144 and 5145 in to the Application Event Log and ERRORLOG in case autogrow of a database and/or transaction log file has timed out or has taken a long period of time to complete.
Author
Publisher Name
www.techyaz.com

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *