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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » How come my log file is still now able to...

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

Rate Topic

Display Mode

Topic Options

Author

Message

curious_sqldba

Posted Tuesday, February 12, 2013 10:12 PM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 4:13 PM
Points: 1,172, Visits: 2,687

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?

Post #1419288

GilaMonster

Posted Wednesday, February 13, 2013 1:02 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:03 AM
Points: 37,735, Visits: 30,004

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 2008, MVP
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

Post #1419327

curious_sqldba

Posted Wednesday, February 13, 2013 8:59 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 4:13 PM
Points: 1,172, Visits: 2,687

GilaMonster (2/13/2013)

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?

Post #1419591

durai nagarajan

Posted Wednesday, February 13, 2013 9:08 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 2:05 AM
Points: 856, Visits: 2,115

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

Post #1419596

PhilPacha

Posted Thursday, February 14, 2013 11:20 AM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 8:23 AM
Points: 323, Visits: 2,007

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.

Post #1420225

« Prev Topic | Next Topic »

Permissions