1

WE have our Autogrowth on our Database set to the following:

  • ROWs Data - Size 25000MB - Autogrowth by 10%, Unlimited
  • Log - Size 345789 - Autogrowth by 10%, Limited to 2097152MB

Database is throwing errors why writing to it. This is the error:

Autogrow of file 'Foo_log' in database 'Foo' was cancelled by user or timed out after 3328 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.

I know I haven't cancelled it but I need to get data into this Database. There are TBs free on the drive the file is but it keeps failing AutoGrowth. We already have Database File Initialization enabled. What do I do to get it back on track?

Improve this question

1 Answer 1

Reset to default
5

The growth interval on your log file is set too large; the log can't grow quickly enough, which causes the transaction to cancel and roll back, which cancels the auto-grow. Then the transaction re-tries, which triggers an auto-grow, which times out, and this repeats forever.

For a database of that size, I would personally set the growth values to:

  • ROWs Data - Size 25000MB - Autogrowth by 1000MB, Unlimited
  • Log - Size 345,789 - Autogrowth by 8000MB, Limited to 2097152MB

Explanation:

Data files can easily grow a little bit at a time; 1GB is what I've found works well for me.

Log file growth is a little trickier (look up "VLF fragmentation" for the messy technical details); the upshot is you don't want this value too low or too high, and using a percentage is just asking for trouble; I typically use 4000MB for medium databases, or 8000MB for large dbs.

Secondary issue: SQL log files shouldn't grow infinitely; are you running in Full Recovery mode? If so, are you running regular log backups?

Improve this answer
5
  • yes are required to have full recovery and it does a backup weekly. Commented Oct 29 at 11:23
  • Are you doing both full backups and log backups weekly? Or full backups weekly and log backups more often? Commented Oct 29 at 14:57
  • 1
    See my answer on this other question for a more extensive discussion of recovery modes and log backups while managing log size. Commented Oct 29 at 15:07
  • 1
    More general discussion of recovery modes and log file management here Commented Oct 29 at 15:16
  • Thank you so much! That was super helpful and got us running. Saving this for future! Commented Oct 29 at 15:57

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.