May 5, 2010 at 9:36 am
Hello,
On a large database which is being written to extensively, received errors "Autogrow of the "dbname_log" in database dbname was cancelled by user or timed out after ___ milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size"
When I try to set "Unrestricted File Growth" it always bumps the log file back to "restricted file growth" of 2,097,152.
This is a large database and the application writing to it failed to save certain records, perhaps due to this.
Please advise.
May 5, 2010 at 9:44 am
May 5, 2010 at 10:06 am
chrisph (5/5/2010)
Hello,On a large database which is being written to extensively, received errors "Autogrow of the "dbname_log" in database dbname was cancelled by user or timed out after ___ milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size"
When I try to set "Unrestricted File Growth" it always bumps the log file back to "restricted file growth" of 2,097,152.
This is a large database and the application writing to it failed to save certain records, perhaps due to this.
Please advise.
First, the max size of a log file is 2 TB, which is what 2,097,152 MB equals.
Second, your transaction log is probably growing by a percentage, say 10%, and the log file large enough that it can't be initialize before timing out. You need to change this to a set size small enough that the log can be initialized properly, but this is just a symptom of another problem.
What is the recovery model of the database, FULL or BULK_LOGGED? If so, how often, if at all, are you runnig transaction log backups?
May 5, 2010 at 10:09 am
May 5, 2010 at 10:18 am
Yes the log file is set to autogrow by 10% (which was 100MB), I have set this to 50MB now.
The database is in FULL recovery mode.
There are no log backups currently (this is more of a test databse), do these "flush" out the logs during backup?
Would it be better to turn off autogrow on this and set it to a large size?
Would setting the recovery model to Simple also fix this?
May 5, 2010 at 10:39 am
chrisph (5/5/2010)
Yes the log file is set to autogrow by 10% (which was 100MB), I have set this to 50MB now.The database is in FULL recovery mode.
There are no log backups currently (this is more of a test databse), do these "flush" out the logs during backup?
Would it be better to turn off autogrow on this and set it to a large size?
Would setting the recovery model to Simple also fix this?
If it's a test DB and DR/BCP are not needed and since you are not doing TLog backups, then your best off to simply change the recovery model to simple
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
May 5, 2010 at 12:14 pm
chrisph (5/5/2010)
Yes the log file is set to autogrow by 10% (which was 100MB), I have set this to 50MB now.The database is in FULL recovery mode.
There are no log backups currently (this is more of a test databse), do these "flush" out the logs during backup?
Would it be better to turn off autogrow on this and set it to a large size?
Would setting the recovery model to Simple also fix this?
Since it is a test database, and probably does not need point in time recovery; yes, change the recovery model to SIMPLE.
May 5, 2010 at 1:50 pm
Please read through this - Managing Transaction Logs[/url]
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply