Hey Jon, let's break down your transaction log questions clearly—this is a super common scenario, so you're not alone in wondering about this behavior!

1. 这个现象完全正常，不用紧张！ #

First off, what you're seeing is exactly how SQL Server's transaction log works in full recovery mode:

• Transaction log backups don't shrink the physical LDF file—they only mark the portions of the log that are committed and backed up as reusable. SQL Server keeps the LDF at its maximum size because resizing files (shrinking then growing again) is an IO-heavy operation that kills performance. It's trying to avoid having to repeatedly ask the OS for more disk space.
• That 13GB first log backup after nightly maintenance? Totally expected. Rebuilding indexes and updating stats generates a massive amount of transaction log—index rebuilds essentially drop and recreate the index, every step of which gets logged. That log has to go somewhere, so the first backup after maintenance will capture all of it.
• The LDF staying at 13GB makes sense too: that's the peak amount of log space your database needs for daily operations plus maintenance. As long as your log backups are running on schedule and marking space as reusable, the log won't keep growing beyond that—this is a healthy state.

2. 目前的情况有没有问题？ #

If your disk has enough space for the 13GB LDF, and the log isn't continuing to grow uncontrollably (i.e., it stays at 13GB after backups), then no, there's no problem. A couple things to watch for though:

• If the LDF starts growing past 13GB, that could mean you have long-running uncommitted transactions, or your log backups aren't running properly—those are issues to investigate.
• Auto-shrinking the log (which we'll talk about next) is a bad idea, but your current setup is behaving as intended.

3. 能不能实现事务日志备份后自动收缩至0？ #

Short answer: Don't do this, and it's actually impossible to shrink to 0 anyway. Here's why:

• The log file always needs to hold active transactions (like any in-progress queries), so you can never shrink it to 0. Even if you try, SQL Server will leave enough space for ongoing work.
• Auto-shrinking causes terrible performance issues. Every time you shrink the log, SQL Server will have to grow it again when new transactions come in. This creates tons of VLF (Virtual Log File) fragmentation, which slows down all log operations and can lead to transaction delays.
• SQL Server keeps the LDF at its maximum size for a reason—it's optimizing for performance by avoiding repeated resize operations. Fighting that logic will hurt your database's speed.

If you're really tight on disk space and absolutely must automate some form of shrinking (again, strongly not recommended), you could add a step to your maintenance plan right after log backups to run:

DBCC SHRINKFILE (N'YourActualLogFileName', 100); -- 收缩到100MB，而非0

Replace YourActualLogFileName with your database's log file name, and pick a minimum size that can handle your typical 30 minutes of log activity (like 100MB instead of 0).

比自动收缩更好的方案 #

与其想着自动收缩日志，不如优化日志管理策略：

• 调整索引维护逻辑：只重建碎片化超过30%的索引，更低碎片化的索引用REORGANIZE命令，后者产生的日志量少很多。
• 给LDF设置固定的自动增长增量：放弃百分比增长（会导致不可预测的文件大小变化），改用固定值比如每次增长1GB，这能减少VLF碎片，让增长更可控。
• 保持当前的日志备份频率：每半小时一次的备份频率已经能很好地及时释放可重用的日志空间。

内容的提问来源于stack exchange，提问作者Jon Weir