Sql-server – SQL Server shrink log after full and log backup

Log files are written to sequentially in a round-robin fashion. The log won't be shrunk beyond the last active portion of the log (in your example above, VLF 41). Once you take a log backup, that VLF is cleared, and the file 'round-robins' back to the first inactive VLF, at which point you can shrink to that one.

https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide

There shouldn't really be any need to change recovery model to achieve this. Either take regular log backups, or if you don't require point-in-time recovery, move to simple recovery.

Finally, once you've shrunk the fragmented log file, grow it to a suitable size with sensible autogrowth settings. It's likely fragmented because it was never sized correctly (1MB?) and has had to grow frequently in (10% ?) steps. Log growth is a slow process and so you want to avoid it for this reason in addition to it causing fragmentation.

(Points 6,7,8 here) https://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/

(No. of VLF's) https://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/

I think there may be some confusion here on the word "Shrinking" - The log file is not ever "Shrunk" by a backup. It can be "truncated" by the log backup, but not shrunk.

When a log is truncated, the segments in the log that are no longer needed for recovery (either because you are in SIMPLE recovery model and a checkpoint has ensured that the data is in the data file securely, or you are in FULL recovery model and the log records have been backed up with a transaction log backup) are effectively marked for overwrite. The log is allowed to use the space those "old/unneeded" log records consumed because they aren't required for recovery.

Note I didn't say "Shrink". The truncation means you can possibly continue writing to the space already allocated to the log file - if there is enough. But truncation will NEVER shrink a log file. It just means it may not have to grow more.

Truncation is more or less an internal log file maintenance type operation. I love what they used to call SIMPLE recovery model back in SQL Server 2000 - "Truncate on checkpoint" mode.

Shrinking is a physical operation on the file itself. I have a few opinions on shrinking data and log files in a rather long answer here. But, in general, don't fear a bigger file. Fear an out of control file that is growing unconstrained. If you are truly taking log file backups, and SQL is showing that you have - you likely just have a file with mostly empty space - meaning the file can be rewritten, and will. It's rather healthy to have a file that is "right-sized" to not have to shrink/grow and shrink/grow between log backups.