Take the 2-minute tour ×
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 past i had a problem of that query been executed for 7 days. i couldnt find any solution. so i created again new database with indexes.

Now i am running a simple query:

UPDATE query

query has been executing from last 26 hours. I really don't understand what to do please help

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

  • Datafile - 40gb and log file - 50 gb autogrowth by 10%

Guys i still have the same problem.....please any one can help me

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

share|improve this question
    
How big are the data and log files? And what are the autogrowth settings? –  Stuart Moore May 31 '13 at 9:52
    
datafile - 40gb and log file - 50 gb autogrowth by 10% –  Lucky May 31 '13 at 9:55
    
Is this running on your desktop PC or an actual server? –  Martin Smith May 31 '13 at 10:37
    
RE: Your edit have you taken any of the steps suggested in the answer? You need to provide more details about the environment you are running this in anyway. Quite possibly it is just inadequate for the task. –  Martin Smith Jun 6 '13 at 11:18
add comment

1 Answer

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
1  
Generally NEVER grow anything y %. My rule is not to grow anything on larger databases anyway (but preallocate) but in general, you should not have percentage growth - those get bigger and bigger. Logs grow by 8000mb is a good thing (8gb is recommended to get 16 vlf anyway). –  TomTom May 31 '13 at 10:45
    
so as i have data file - 40gb and log file - 50 gb auto growth by 10% How much auto growth shall i change to ? –  Lucky May 31 '13 at 10:48
1  
TomTom's suggestion of 8GB would be good if you weren't experiencing what appears to be problems with slow disk when allocation 5GB. So maybe try with 2GB? The other option as mentioned is to create your database with a 50GB transaction log. Then you'll only have to wait while it's created then, rather than while executing your query. –  Stuart Moore May 31 '13 at 10:53
    
@Lucky in our larger environments we set a small autogrowth, like 128 MB and monitor. If the database starts using autogrowth we will bump it up by a few GB during off hours. You generally do not want your databases growing automatically if you can help it –  Cougar9000 May 31 '13 at 11:24
    
@cougar9000 - so you suggest its a good idea to set a small autogrowth - 128 mb –  Lucky May 31 '13 at 11:26
show 1 more comment

Your Answer

 
discard

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.