Msg 5145: Autogrow of database log file took 1010871 milliseconds

Question

In the past, I had an update query that ran for 7 days. I couldn't find a solution, so I created a new database with indexes. The update query has now been running for 26 hours.

I am seeing messages like the following:

Autogrow of file database1_log file in database 'database1' took 1010871 milliseconds. Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.

Autogrow of file 'database1.log' in database 'database1' took 79342 milliseconds. Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.

• The data file is 40GB
• The log file is 50 GB, with autogrowth set to 10%

I really don't understand what to do.

Answer 1

The error is caused by how long it's taking SQL Server to grow your transaction log.

Assuming the error message you've quoted is for the last grow, SQL Server is claiming that it's taking about 17 minutes for SQL Server to add just under 5GB to your transaction log file.

This can be caused by a number of issues:

• Slow disk: If your disks are particularly old and slow it will take SQL Server longer to extend the files

• Disk Contention: If SQL Server is having to compete for access to the disks, then it will be slow to extend them. Common causes of this could be having only 1 disk for OS, Data and Logs, or another application on the machine accessing the same disk as SQL Server

There are a number of things you can try to alleviate the problem:

• Move your files onto seperate physical disks if possible. Typically you'd like to have 3 disks for OS, Data and Logs.

• Use faster storage. If you can upgrade your disks then this will alleviate the wait for autogrowth to complete

• Modify your autogrowth setting to be a fixed size. That way you'll get consistent growth rather than exponential as you are with your current percentage setting. Pick a value that is an acceptable compromise between lots of file growth events and waiting for the growth to happen.

• Recreate your database, and preallocate the log file required to accommodate the queries you plan to run. This will remove the autogrowth events (unless you exceed that size).

Hope that helps.