Why can’t I remove this extra transaction log file?

Before this post gets started, let’s get past the title. I know what many of you are thinking: what in the name of Paul Randal would make someone have an EXTRA log file? The short answer is: desperate times.

True, there’s no advantage to having more than one log file, but sometimes that one file grows suddenly and fills up the drive in the middle of a transaction and you’re stuck with those dreaded “THE DATABASE IS DOWN!!!” tickets until that transaction finishes. So, in the heat of the moment, you hit the panic button and create ANOTHER log file on a different drive.

Then, minutes, hours, or even weeks later, you want to put the universe back in order by resizing the original log file and removing the extra one. But what if you find you can’t remove that extra one, no matter what you try to do?

That’s what happened to me. Let me show you how I solved this riddle.

Oh My Golly

I am a Central Management Server fanboy, because if I find an issue on one SQL Server instance, it allows me to quickly check every other instance in my environment for the same issue. This happened recently when we I found a database with two log files. I said to myself, “Self, you should probably check to see if any other databases have some lingering second log file.” These kinds of ponderous ruminations are crucial to being an effective SENIOR Database Administrator.

If you would like to check for extra log files in your own environment, you could try a little something like this.

-- doing this old school style
IF OBJECT_ID('tempdb..#LogFiles') IS NOT NULL
       DROP TABLE #LogFiles;
GO
 
-- i'm optimistic no database has more than 255 log files
CREATE TABLE #LogFiles (DatabaseName sysname, LogFileTotal tinyint);
 
-- survey the databases
EXEC sp_msforeachdb 'INSERT #LogFiles(DatabaseName, LogFileTotal)
SELECT
 ''?''
, COUNT(file_id)
FROM [?].sys.database_files
WHERE type_desc = ''LOG''';

-- and survey says...
SELECT
DatabaseName
, LogFileTotal
FROM #LogFiles
WHERE LogFileTotal > 1
ORDER BY DatabaseName;
GO

I ran this T-SQL and found another database with two log files. Sad trombone. (That wasn’t the database name, but let’s go with that.)

Oh well, I thought, I can just empty the extra log file and remove it. I figured this could be done with something like this script.

USE SadTrombone;
GO
CHECKPOINT;
GO
DBCC SHRINKFILE (N'SadTrombone_Log2', EMPTYFILE);
GO
ALTER DATABASE SadTrombone REMOVE FILE SadTrombone_Log2;
GO

I ran this, but I did not get a return message of “Commands completed successfully.”

I got “Msg 5042, Level 16, State 2, Line 1 The file ‘SadTrombone_Log2′ cannot be removed because it is not empty.”

Wait…what?

Where Is My Mind?

I checked for open transactions. None. I checked for any other connections. None. I even checked this:

SELECT log_reuse_wait_desc FROM sys.databases;

Literally “NOTHING” was the result. What was going on? I decided to take a closer look using this T-SQL…

DBCC LOGINFO('SadTrombone');

…which showed me something I’d never seen before.

If aren’t familiar with this command, know that it shows information about Virtual Log Files. Those are the segments within each log file, and the transaction log for your database must always have at least two of them.

(Side note: If you your transaction log uses the default settings used in the ‘model’ database, you might have A LOT more than two of them. In that case, you would do well to read this VLF post by Kimberly Tripp very, very soon.)

But to the point, as you can see in the image above, my transaction log has two VLFs, but there is only one in each transaction log file. Trying to remove either one is verboten because it would only leave 1 VLF, and SQL Server ain’t havin’ none of that.

Did you know you could have a log file with one VLF? I certainly didn’t.

But there was an easy solution to this: just grow one of the files.

ALTER DATABASE [SadTrombone] MODIFY FILE (NAME = N'SadTrombone_Log', SIZE = 73728KB);

I ran that, then check the VLFs again.

DBCC LOGINFO('SadTrombone');

Aaaah. Now we’ve had more than two VLFs in the main log file (FileId = 2), I was able to drop the second log file without issue.

I’m Amazed

The only way I can think we ended up with 1 VLF in each file would be because at some point someone ran SHRINKFILE commands against both of those database files, reducing them to 1 VLF. But I don’t know for sure.

At any rate, hope this helps if you encounter this issue some day!