0

My 2005sql server doesn't respond to the query sometime , and when I check the event I get the following event : Auto grow of file "databasename_log" in database was cancelled by user or timeout after 30125 milliseconds . Use alter database to set a smaller file growth value for file or to explicitly set new file size At moment for resolving this problem I must restart the sqlserver , I checked the sqlserver _log file the auto grow was not disable and the size was 10% What can I do for this problem?

parsa
  • 1
  • 3
  • How big is your logfile at the moment? I would suggest to shrink it after each daily(?) backup. So a 10% grow would not take that much time – Scoregraphic Aug 31 '10 at 08:07

2 Answers2

1

You need to get rid of that 10% autogrowth on your log file and do some analysis of what is going on with your database files.

<rant>Microsoft should get rid of that stupid default 10% autogrowth setting.</rant>

What happens over time is that as your log file grows and grows, especially for a database in full recovery mode when regular backups are not being taken, the amount of storage that an autogrow tries to allocate will get bigger and bigger.

Set the autogrowth to a fixed amount (you'll need to determine how much that is based on the size of your files) instead of a percentage. Be careful not to set it to something huge that your I/O can't allocate in a reasonable amount of time.

For more on tuning autogrowth settings have a read through this:

squillman
  • 37,883
  • 12
  • 92
  • 146
0

I would suggest to make your log file larger, this way the file don't need to grow during the query which saves a lot of time and will greatly improve performance. I suggest you do the same for your database files.

WilfriedVS
  • 876
  • 6
  • 3