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:
- What will happen to the log file once this limit is reached?
- 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).
Best Answer
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
CHECKPOINToperations. 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.