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.
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.
- Enable Instant File Initialization to speed up data file size allocation. Read attached article to enable IFI (Instant File Initialization) on your database server.
- As IFI (Instant File Initialization) does not work on log files so we need to set log files in smaller chunks to avoid any time out error or longer runs.
- Set database size to the maximum size it could be and allow autogrowth settings to occur only in unplanned situations for space.Read attached article to understand Autogrowth setting and its perfect value for database files.
I hope you like this article. Please follow our Facebook page and Twitter handle to get latest updates.
Related Articles:
- Manage Transaction Log File during Data Load
- How to Improve Bulk Data Load Performance?
- How to Reduce SQL Server Wait type PREEMPTIVE_OS_WRITEFILEGATHER?
- Why Should You Always Turn Off Database Auto Shrink Property?
- Fix Error 3023: Shrink failed for Log file
- How to Fix SQL Error 1005: A Comprehensive Guide - April 9, 2023
- How to Fix SQL Server Error 207 – Invalid Column Name - April 9, 2023
- How to Fix SQL Error 1045: Resolving Access Denied Issues - April 8, 2023