Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
Is this running on your desktop PC or an actual server? –  Martin Smith May 31 '13 at 10:37

1 Answer 1

up vote 5 down vote accepted

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.