The Curious Case of: the un-droppable transaction log file

Question: I had to add a second log file to my database because the log file ran out of space and I was unable to perform a transaction log backup. Now I can’t drop the second log file. Can you help?

Answer: Adding a second transaction log file temporarily is quite acceptable when the log file runs out of space. If the log cannot be cleared (usually by a transaction log backup) then the alternatives really come down to adding another log file or switching to the simple recovery model (and breaking the log backup chain).

Once the crisis has passed, the second log file should be removed again. In this case, when the ALTER DATABASE was issued to drop the second log file, the error returned was:

    Msg 5042, Level 16, State 2, Line 1    The file 'DBMaint2008_log2' cannot be removed because it is not empty.

Looking at log_reuse_wait_desc in sys.databases to see why the log cannot be cleared shows NOTHING.

So how can this be? Well, it could be that the currently active VLF (Virtual Log File) happens to be in the second file – there always has to be at least one active VLF. Looking at DBCC LOGINFO to see the VLF status shows the following:

Ah – this is the problem! The log files each only have a single VLF, and the entire log must always have at least two VLFs. This means the second log file cannot be dropped as that would leave the entire log with only a single VLF.

The trick here is to grow the first log file so that it has more than one VLF and then the second log file will be able to be dropped. If you want to know what all the DBCC LOGINFO output means, see my blog post.

But how did the database get into this state in the first place? Turns out that it’s pretty simple to engineer this case and it involves shrinking each log file when they have VLFs larger than 1MB. The script below will produce the one-VLF-per-file case I’ve described here:

    USE MASTER;    GO    IF DATABASEPROPERTYEX ('DBMaint2008', 'Version') > 0        DROP DATABASE DBMaint2008;    CREATE DATABASE DBMaint2008 ON PRIMARY (        NAME = 'DBMaint2008_data',        FILENAME = N'D:SQLskillsDBMaint2008_data.mdf')    LOG ON (        NAME = 'DBMaint2008_log',        FILENAME = N'D:SQLskillsDBMaint2008_log.ldf',        SIZE = 5MB,        FILEGROWTH = 1MB);    GO    ALTER DATABASE DBMaint2008    ADD LOG FILE (        NAME = 'DBMaint2008_log2',        FILENAME = N'D:SQLskillsDBMaint2008_log2.ldf',        SIZE = 5MB,        FILEGROWTH = 1MB);    GO    USE DBMaint2008;    GO    SET NOCOUNT ON;    GO    DBCC SHRINKFILE (2, 1);    GO    DBCC SHRINKFILE (3, 1);    GO    DBCC LOGINFO (DBMaint2008);    GO