SQL error Msg 5144 SQL error Msg 5145

SQL Issues and Solutions

The SQL error Msg 5144, SQL error Msg 5145 generally occurs due to timeout issues in the process of extending the data or transaction log files or during autogrowth operation for data or log files.

Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to fix SQL related issues.

In this context, we shall look into the main causes of this issue and how to resolve it.

What triggers SQL error Msg 5144, SQL error Msg 5145?

Basically, If the size of the data is extended or log file with a larger value or if the database performs autogrow operation to extend its database files with bigger value, then SQL Server takes longer time to perform such activities.

Due to this delay, the SQL Server reports Msg 5144 and Msg 5145 errors in 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 for the process to complete.

In the case of SQL error Msg 5144, you will see the following report;

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.

And for SQL error Msg 5145, you will see;

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.

How to fix SQL error Msg 5144, SQL error Msg 5145?

To resolve these SQL issues, follow the following steps;

i. It is necessary to know about size growth of files as this will help to prevent SQL 5144, 5145 error.

ii. Never you rely on autogrowth of files as a standard operation for obtaining more space in a file. It is much more better to plan the maximum database size and allow autogrowth to occur only in unplanned situations for space.

iii. It is recommended to enable Instant File Initialization which helps to speed up data file size allocation.

iv. IFI(Instant File Initialization) does not work on transaction log files. So we can set log files in smaller chunks to avoid any time out the error or longer runs.

v. Ensure that the disk performance is great.