How to remove secondary log file when SQL thinks it isn't empty

Question

I had to add a log file to my database to do a one-time load of data in my SQL Server 2019 database in simple recovery model. I added the file with

ALTER DATABASE db ADD LOG FILE (NAME=log2,FILENAME='C:\filepath\log2.ldf',SIZE=100GB,MAXSIZE=100GB,FILEGROWTH=0MB)

Now that I've finished my load, I attempt to remove the file with

ALTER DATABASE db REMOVE FILE log2

However, I get the error message

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

Things I've tried from my research and some ideas...

I've checked DBCC loginfo(db) which shows a status of "0" for the new file, which should mean unused.

I've also checked DBCC OPENTRAN which returns "No active open transactions".
I tried taking a full backup of the database, same error. Tried manually running CHECKPOINT command, same error. Checking sys.dm_db_log_info(DB_ID('db')) shows that my empty file has no active VLFs.

Tried restarting the SQL instance, same error. Tried putting the database in single user mode, same error. Tried setting the database to read_only, new error:

Msg 5004, Level 16, State 3, Line 3
To use ALTER DATABASE, the database must be in a writable state in which a checkpoint can be executed.

Which suggests that my next attempt at setting the database offline will also fail... and it does:

Msg 942, Level 14, State 4, Line 3
Database 'db' cannot be opened because it is offline.

Tried shrinking the file with

DBCC SHRINKFILE(log2,0) and DBCC SHIRNKFILE(log2,EMPTYFILE) 

and it got stuck around 800,000 pages.

I also tried switching to full recovery mode, taking a log backup, then removing the file which also didn't work. Switching back to simple and attempting to remove the file still gave the same error.

And it's at this point I ran out of ideas. When I mock this in a simple fashion in a separate SQL 2022 (add file, insert some rows, remove file) it works without issue.

Answer 1

Note that the following does destroy whatever was in the log, so can cause corruption if the database is not in a stable state with no transactions in the log.

Was able to finally get this resolved by detatching the database-

USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'db'

Then removing both log files from the filesystem.

Then reattaching the database -

CREATE DATABASE [db] ON 
(FILENAME = N'F:\db\db1.mdf')--don't include the log files
FOR ATTACH_REBUILD_LOG

which gave the warning message

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\testdatabase1_log.ldf" may be incorrect.
File activation failure. The physical file name "C:\temp\log2.ldf" may be incorrect.
New log file 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\testdatabase1_log.ldf' was created.

Then I moved the file to the previous location, update the metadata with

ALTER DATABASE db MODIFY FILE(name='log',FILENAME='F:\db\log1.ldf')

and all was good.