2

I have a database that is on Full recovery mode.

I previously made a full backup, and then on a daily basis add differential backups.

Now I have backed up the transaction log for the first time, which reflected in the greatly increased file size of the .bak file. However the log file in the \DATA directory is still the same size.

I was under the impression that the transaction log file would automatically be truncated on a log backup, as mentioned here and elsewhere.

Previously I asked a question on Stack Overflow (here) where the answer indicated that the log file info was not up-to-date with the actual log on disk. Is that the case with this observation?

If so, how can I get the Windows file to refresh to display up-to-date data?

Improve this question

1 Answer 1

Reset to default
4

The transaction log does not automatically shrink because you did a backup. This is actually a good thing because processes actually stop when the log grows. Your log has grown to the current size because you had processes that required the log to grow to that size. These could have been long running units of work that generated a lot of log records (like index maintenance). The log 'truncation' you're referring to means that, after a log backup, log space is available for 'reuse'. Again, this process does not automatically reduce the size of the log. To reduce the log size,, you'd need to 'shrink' the log file. Repeated shrinking of the log is discouraged because it typically just re-grows, which affects performance. If you do 'shrink' the log, you should monitor the growth rate. Here is a helpful article. I also found this article that provides additional information.

Improve this answer
6
  • Thank you that is helpful. In this case the transaction log is over 5GB (on a database that is 2GB). In the DB properties, I see that the transaction log is limited in size to a mere 2 000 GB (on a hardrive that is 500 GB). What would happen if I were to shrink the transaction log, and then change the maximum log size to 1GB (along with changing the mode to simple). I.e. If the max size of a log is exceeded... Would the database stop doing transactions?
    – Zach Smith
    Oct 21, 2016 at 10:00
  • 1
    Yes, and it will be raising errors about transaction file being full. Details: msdn.microsoft.com/en-us/library/ms175495.aspx
    – Marcin S.
    Oct 21, 2016 at 10:04
  • 2
    If you actually provide a maximum size for the transaction log OR you attempt to grow the log to more than the available space on the drive where the log is located, transaction for that database will stop and you'll start receiving log full errors. You should think carefully before changing to SIMPLE mode as that prevents recovery to a 'point-in-time'. Also, SIMPLE mode would still grow the log to account for long running transactions.
    – Scott Hodgin - Retired
    Oct 21, 2016 at 10:05
  • @ScottHodgin, the database is updated every morning. point-in-time recovery is not required
    – Zach Smith
    Oct 21, 2016 at 10:07
  • 1
    I would 'suggest' that 'limiting' the size of the transaction log is a 'little' dangerous. I think you'd be better off with 'unlimited' and setting up Alerts for file growth - sqlservercentral.com/articles/alerts/89885
    – Scott Hodgin - Retired
    Oct 21, 2016 at 10:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.