Timeout Expired - Autogrow of file cancelled or timed out

Question

Timeout Expired - Autogrow of file cancelled or timed out

Senthil T

Hall of Fame

Points: 3367
More actions
December 6, 2005 at 5:19 pm

#152655

Hi,
I am getting the following timeout error - "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."
I checked the application event viewer, there is a list of messages from MSSQLSERVER. This is the description - "5144: Autogrow of file 'USL_Log' in database 'USL' cancelled or timed out after 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a new size."
I was getting this error this afternoon. I increased the log file 'file growth' from 10% to 25%. I dont know if that did the trick, but I was able to access the website without any problem. There was no error for next couple of hours. Now it has started again, it is throwing an error 9 out of 10 times. Can any one tell me why is this happening and how to correct this?
Thanks.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Senthil T Hall of Fame Points: 3367 More actions · Answer 1

I tried to execute a simple user login stored procedure from the query analyzer, it took for ever to complete the execution. It did not complete the execution after 5 mins, I had to stop it.

David Webb-CDS SSCoach Points: 17398 More actions · Answer 2

Looks like the autogrow increment may be running the disk out of space. Check to see if you have enough room to do a 25% (or a 10%) growth on the drive.

And then again, I might be wrong ...
David Webb

Senthil T Hall of Fame Points: 3367 More actions · Answer 3

Senthil T

Hall of Fame

Points: 3367

December 6, 2005 at 7:12 pm

#608106

There is more than 30GB of free space. Anyone?

hplu-243427 SSC Enthusiast Points: 151 More actions · Answer 4

I don't have similar experience, but from what You tell in Your previous posts, I would recomend that You decrease the growth value instead of increasing it. Try to set it to a fixed value that is at least 50 pct lower than what the 10% growth would have given. The cause could be slow i/o sub system caused by other processes/applications doing i/o against the samevolume/diskset or other factors such as physical fragmentation. Use performance monitor to get details regarding disk i/o during the autogrow. From what I know, growing (extending) a file is one of the most resource demanding operations that SQL Server has to deal with and it could be influenced by a lot of "external" factors.

\hplu

Jane Matheson-154829 SSCertifiable Points: 5522 More actions · Answer 5

Suggest same thing as hplu. Had a similiar problem with data file on a web site. Change your growth increment on all files to a set amount you know can be accomplsihed within the timeout period. The autogrow rolls back when the transaction that caused it rolls back. Then manually grow your database to a size you think is sufficient for growth. The autogrow is your insurance against growth past what you estimated.

Jane Matheson-154829 SSCertifiable Points: 5522 More actions · Answer 6

BTW, if it's a log file giving the error, you may be fooled into thinking you fixed a problem if TLOG backups are running at intervals and you don't know when. The root of the problem may be that your log file is growing beyond what was planned. You need to check on your backup strategy and make sure it's running as expected and understand when the log file should be clearing out. You might have long running transactions keeping the log from clearing properly. Replication issues can have an effect on the log also causing the growth of the log file beyond what you expect.

tejas parikh Old Hand Points: 321 More actions · Answer 7

How big is your log file. MS suggests that instead of growing it by 10 or 25% of x gb, you should turn it up by 100 mb or so at a time which would not give you the error. Try it...

Senthil T Hall of Fame Points: 3367 More actions · Answer 8

Actually I changed the file growth to 5%. Looks like it is working fine.

The log file size is 9GB.

Can any suggest any steps to avoid the same thing happening in future.

Can I do a backup like this - copy the USL_Log.LDF file into someother location and create an empty USL_Log.LDF file. Will this work?

Thanks.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 9

'autogrow' is a nice feature but not without it's pitfalls as you've seen. I always use 'autogrow' but in a slightly different manner. I alow database data and transaction log portions of the database one 'autogrowth' only. It is 50% of the initial allocation and have not had a problem since this feature was introduced in v7.0. That is not to say that you will have to do additional homework first in the initial sizing of your databases and monotoring their growth. The data portion is more problematic, but transaction logs (with regular backups every 15 minutes) generally do not need much attention.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

tejas parikh Old Hand Points: 321 More actions · Answer 10

http://support.microsoft.com/default.aspx?scid=kb;en-us;822641

This is what MS has to say about your problem.

I know it helps because I had the same problem you did. But if you do ur Tlog backups so often as Rudy mentioned, then you should not have this problem.

Regards,

Tejas

Senthil T Hall of Fame Points: 3367 More actions · Answer 11

Can you tell me how to set up a transaction log backup for every 15 mins?

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 12

Use a SQL Maintenance Plan.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Jason Emdin Newbie Points: 7 More actions · Answer 13

Thanks for the suggestions, we had the exact same problem here, we even tried to reboot the server (like that was gonna help! 😛 ). Anyway, after changing the growth percentage to 5 for both data and transaction files, the problem went away.

Thanks again!