Hidden away in master.sys.databases is one of the coolest diagnostic tools you might ever need:
SELECT name, log_reuse_wait_desc FROM sys.databases;
This spiffy little snippet will tell you why each database’s log file isn’t clearing out. Possible reasons include:
- Log backup needs to be run (or if you could lose a day’s worth of data, throw this little fella in simple recovery mode)
- Active backup running – because the full backup needs the transaction log to be able to restore to a specific point in time
- Active transaction – somebody typed BEGIN TRAN and locked their workstation for the weekend
- Database mirroring, replication, or AlwaysOn Availability Groups – because these features need to hang onto transaction log data to send to another replica
That’s it. That’s the whole blog post. Listen, they can’t all be 2000-word masterpieces.