Fix transaction log file after database update

Question

I have a databases that are regularly (monthly) updated. An update includes a lot of new or changed T-SQP procedures, functions, indexes creation, etc.

A database X has transaction log file with the following settings:

• unlimited growth
• initial size 128 MB
• growth with 128 MB

with space used - maximum 5-10% of it (a backup of the transaction log file is made every 15 minutes).

So, the above settings are specific for each database but in common, the log file size is enough and not growing. After UPDATE is made, the size of the transaction log file is increased with 1 or 2 or 3 GB. Since I do not need such big file for regular use of the database, I am shrinking them back like this:

DBCC SHRINKFILE(2,TRUNCATEONLY)
ALTER DATABASE X MODIFY FILE (NAME = N'x_Log', SIZE = 128MB)

I want to execute the code above after each update, but sometimes I need to execute the command above many times in order to reduce the database tlog file (each time I have to wait 15 minutes for the tlog backup to complete).

The database are in FULL recovery mode with Always ON, end the tlog backups are made on the secondary databases.

Could anyone tell if the idea for regular shrink is bad and why sometimes I need to executed the shrink several times?

Answer 1

Short answer:

Just leave the log file as big as it typically needs to be, and stop worrying about it.

Longer answer:

First, why do you want to keep shrinking the file? If it's just going to grow again (and keep in mind that shrink and grow operations are expensive, especially for the log), then what did you gain? What did you use all that freed up space for in the meantime? Please read this page, in full, this article, and this blog post, and then come back with further questions. You're not seeing 8 million people on the Internet tell you not to shrink for no reason: it's a really bad and pointless exercise in almost all cases. There are exceptions, but this does not sound like one of them.

Sometimes I need to execute the shrink multiple times

I've addressed the primary reason for this in this answer. Essentially, SQL Server has to have two checkpoints or two log backups in order to wrap around to the reusable portion of the log. (This has to do with the number and arrangement of the VLFs in the log file, and where the current LSN points. We could post a book here but the easy answer is to just do what you're doing or run an additional log backup before starting.)

Another possibility is that the log can't be reused for a variety of reasons. Next time you try to shrink the file and it doesn't shrink on first try, check sys.databases.log_reuse_wait_desc. For example, if you're also using replication on this database, this can be caused by that activity, an un-replicated transaction, etc.

But usually it's just the wrap-around thing. So next time, issue two manual log backups - I don't see why you need to wait for the 15 minute interval (unless additional, smaller log backups in between will throw off any manual processes you have around your AGs). Or (as per above) just stop performing this pointless work - especially since that work is done over and over on every replica. Once again, this is useless effort you're putting in with the net result of: you freed up a little disk space for a little while.