How come my log file is still now able to grow?

Question

How come my log file is still now able to grow?

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
February 12, 2013 at 10:12 pm

#274118

Initial log file size is 5gb, 10 % file growth, Auto Growth enabled, unrestricted growth checked and drive has plenty of space . Still i see below error message:
Autogrow of file 'MyDb_Log' in database 'MyDB' was cancelled by user or timed out after 3837 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.
When this occured i noticed a lot of blocking on MyDb,probably because file wasnt able to grow. My question is even though all the options were checked why didnt the log file grow? I was able to resolve the issue by changing growth rate to 512mb instead of 10 %. Any thoughts what could be causing this? Bad indices/stats?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You've probably got a % growth like 10% (very bad default) and an IO subsystem under strain so that the log can't grow and zero-out the requested size in the time allowed.

As the message says, use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 2

GilaMonster (2/13/2013)
You've probably got a % growth like 10% (very bad default) and an IO subsystem under strain so that the log can't grow and zero-out the requested size in the time allowed.
As the message says, use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.

I did have the growth rate to 10% with initial size as 5GB, so the time when i had this issue the file would have grown to 500 MB, when i changed the growth rate to fixed 512MB issue was gone?

durai nagarajan SSCoach Points: 15778 More actions · Answer 3

what is your database size now? do you have log backup?

Have you understood completely, what Gail said? if your DB is 500GB it will try to increase by 50GB which is a huge bottle neck.

Regards
Durai Nagarajan

PhilPacha Hall of Fame Points: 3645 More actions · Answer 4

You keep mentioning the 'initial size'. That only matters at the beginning. How big was the log when the failed increase was attempted? 10 percent of *that* amount is what the system was attempting to allocate and zero - not of your *initial* size.