A log file not shrinking is explained by one column: log_reuse_wait_desc. A demo database reads LOG_BACKUP and ACTIVE_TRANSACTION from it and shows why the first shrink after a log backup can still fail. A table lists the other reasons with the action for each.
Free Space in Database Files: Check Data and Log Files
To see the free space in database files, compare each file’s size with the pages it uses. This post gives one query for data and log files, adds the free space on the drive, and checks the log figure against a second source.
Recovery Model Changes and the Log Backup Chain
Explain the log backup chain across FULL and SIMPLE changes, inspect recovery LSNs and backup history, and establish a new backup boundary.
SQL SERVER – Delayed Durability and Flushing Log Files
Flushing with sys.sp_flush_log hardens preceding committed delayed-durable transactions in the current database. I use delayed durability only with an accepted tradeoff.
SQL SERVER – Running Log Backup While Taking Full Backup
A full backup and log backup can overlap on one SQL Server database. A customer noticed both during a slowdown.
Remove Extra Log File in SQL Server: Fix Error 5042
To remove extra log file members, SQL Server needs the files to hold no active log. When one does, ALTER DATABASE REMOVE FILE fails with Msg 5042. This post builds a database with two log files, reproduces the error, shows which VLFs keep the file busy and frees it with log backups. It also explains why the file stays offline until the next log backup.
How to Solve Error When Transaction Log Gets Full? – Interview Question of the Week #272
Question: How to Solve Error When Transaction Log Gets Full?
Answer: This was a question I was asked in the recent Health Check.