2

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).

Mike Richards
  • 123
  • 1
  • 1
  • 4
  • Actully off topi here - there is a special site for database questions. – TomTom Sep 29 '14 at 16:54
  • 1
    @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. – phoebus Sep 29 '14 at 17:09

2 Answers2

1

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.

phoebus
  • 8,370
  • 1
  • 31
  • 29
  • 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? – Mike Richards Sep 29 '14 at 15:51
  • @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. – phoebus Sep 29 '14 at 15:53
0

What will happen to the log file once this limit is reached?

You are in trouble. Transactions fail., That isa 1gb rollback on likely a system not prepared to handle the load - will take literally ages of your assumed time.

Is there any impact of setting a smaller max limit (say 100 MB rather than 1024 MB)?

You run out of space earlier. WHich emans certain operations MAY fail.

Note sure how tiny the databases are you work with, but I

  • ALWAYS preallocate them to max size and
  • Make sure they have breathing room.

Not working with simple here, but I have a datbase running 500mb per 15 minute interval (we back that up every 50 minutes) and the tx log is 400gb for the occasional ETL job. It does cost me literally cents compared to the trouble and performance loss of growing file (as well as the rel internal ragmentation you cause). Autogrow is ONLY suitable for "low end small databases".

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).

With simple revcovery mode they are truncated on every checkpoint pretty much. Read up what you have configured.

TomTom
  • 50,857
  • 7
  • 52
  • 134
  • 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. – Michael Green Oct 19 '14 at 03:03
  • 1
    @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. – TomTom Oct 19 '14 at 08:42
  • Nice sarcasm there @TomTom. Good luck with that. – Michael Green Oct 19 '14 at 22:13