SQL Server Transaction Log maximum file size

Question

I have several databases, all in Simple recovery mode. There have been previous concerns about the log file sizes and I have shrunk them all using Management Studio's Shrink task. I have also placed a setting for the logs autogrowth to be a maximum size of 1024 MB. I am expecting the log file to continue to grow as needed until it is 1024 MB. I have two questions at this point:

1. What will happen to the log file once this limit is reached?
2. Is there any impact of setting a smaller max limit (say 100 MB rather than 1024 MB)?

It should be noted that there is also a backup maintenance plan to be performed 2 times a day and the logs should automatically be truncated (as opposed to shrinking).

Solution

If your log file reaches its limit in size during a transaction and cannot autogrow then the transaction won't be able to commit and you will see errors in SQL. The log file needs to be sufficiently sized to handle the transactions in between CHECKPOINT operations. Setting a lower limit increases the likelihood that you will run into trouble due to a large transaction (workload dependent).

Your log file will grow naturally to the size that is required, assuming that sane autogrowth parameters are set (note that as TomTom suggested it's probably better to start with a reasonable size rather than wait for autogrowth to take you there, robbing you of performance in the meantime). Typically the only time you should be shrinking a Simple mode log file is if you have recently performed some kind of bulk transaction that caused the log file to grow to a much larger size than normally needed, and you need to reclaim that space for some other use.

If you find yourself needing to shrink your log files frequently you need to either add more space to the system or move whatever other services require that space to another server.

Comments

[TomTom]

Actully off topi here - there is a special site for database questions.

[phoebus]

@TomTom I would think that basic RDBMS questions (which this is) would remain on-topic at SF, just as a basic ACL question isn't relegated to the security SE site. This sort of thing often falls to the sysadmin as it doesn't really require any DBA knowledge/expertise.

[Mike Richards]

When a checkpoint occurs and the database is backed up, the logs are truncated from what I understand. Does this mean that the allocated space can be "reused" by new transactions with the possibility of the log files not needing to grow for that checkpoint cycle?

[phoebus]

@butteredtoast In simple mode, committed transactions are flushed from the log on a CHECKPOINT. This allows that space in the log to be reused. The size of the simple mode log file is basically dictated by the size required in between each CHECKPOINT. Note that this has nothing to do with a database backup. Log backups flush transaction logs in Full recovery mode. Be careful not to conflate Full and Simple.

[Michael Green]

The time to rollback a transaction will be roughly the time taken to do the original work. In other words, if your SQL has been running for, say, 30 minutes since the last COMMIT, and it hits an error it will take about 30 minutes to rollback the changes.

[TomTom]

@MichaelGreen I really hate to kill your delusions but SQL Server may take a LOT longer to rollback a transaction. Been in that case where a a 4 hour load resulted in a 16 hour rollback upon failure.

[Michael Green]

Nice sarcasm there @TomTom. Good luck with that.