SQL SERVER – ERROR: Autogrow of file ‘MyDB_log’ in database ‘MyDB’ was cancelled by user or timed out after 30121 milliseconds

One of the most difficult and haunted issue for any DBA is to do a root cause analysis or RCA of a performance issue with SQL Server. That is why monitoring of SQL Server is very important and companies spend a lot of time in buying tools for that. Sometimes, when there is no monitoring then we have to look at all possible logs available. I have been a big believer of checking SQL Server ERRORLOG for any unexpected behavior. Sometimes we do get good piece of information from there. If you don’t know the location of ERRORLOG file, then you should read below blog.

SQL SERVER – Where is ERRORLOG? Various Ways to Find its Location

Below is one of the error message that I have seen in my one of my client’s ERRORLOG file.

Autogrow of file ‘<File Name>’ in database ‘<Database Name>’ was cancelled by user or timed out after %d milliseconds.  Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.

The error indicates that the transaction/session cannot wait until the file growth completes and then the session is been cancelled. Due to that, the file growth is also cancelled. The reason is that the file growth cannot complete in time. In above message, we need to note few thing: a) which file, b) which database and c) how much time before timeout. When the database file (MDF or LDF) is full and it is set to grow automatically, the growth would kick in and transaction would be halted until growth is complete. The time taken to grow would be dependent on two factors. 1) size of growth 2) Disk speed.

Let’s assume that database transaction log size is 50 GB and the file growth is 10%, it will grow by 5 GB. The time needed for this size increase exceeds the time of the session which it can wait. Most of the application have timeout of 30 seconds, which is more than enough for auto-grow to complete. If the disk can response much faster, the issue may not appear at all.

A seasoned DBA performs the transaction log backup regularly and avoid such a large size of transaction log. With a large transaction log, sometimes it can also cause a long database startup time after a restart if some problem happens previously. To avoid the issue in our case, we can try to set the file growth to a smaller value such as 1GB or 500 MB. Microsoft has always recommended doing manual growth so that size is in predictable range. Autogrow is like a band-aid. As per SQL industry best practices, we should avoid file auto-growth and initialize the file to a reasonable size according to the application requirement.  Then we can leave the file growth to 1GB or a large size according to the disk response time. This applies to both data file (MDF or NDF) and transaction log file (LDF).

Since SQL Server 2005, SQL Server has a feature called “instant file initialization” which can accelerate the file growth. Its important to keep in mind that only data files can be initialized instantaneously by this feature. Instant file initialization would be activated when the SQL Server (MSSQLSERVER) service account has been granted SE_MANAGE_VOLUME (Perform Volume Maintenance Tasks). https://technet.microsoft.com/en-us/library/ms175935.aspx

To summarize:

• Increase the file size in a smaller value. Prefer manual growth or fixed size auto-grow.
• Consider providing “Perform Volume Maintenance Tasks” permission to SQL Service account. This would allow “instant file initialization” for faster growth of data files.
• Perform transaction log backup regularly so avoid huge size transaction log in full recovery model.
• Make sure disk performance is good.

Have you ever seen such errors on your production environment?

Reference: Pinal Dave (https://blog.sqlauthority.com)