How do you eliminate extra transaction log files?

Question

I started a new job taking over for a guy that really did not know what he was doing. He created multiple transaction log files for most of the databases on the server. Apparently, he did not know how SQL Server worked. I guess he read somewhere that, since the database can have multiple data files, the logs can do the same.

I have dealt with SQL Server since 1995, and I have never seen this before. So, does anyone have experience in removing excess log files? It is difficult to tear apart a production Database that is 45 TB in size. I need to fix this before moving the Availability Groups. So, this is one of the things I need to cleanup.

Answer 1

Backup transaction log

backup log yourDatabase to disk='C:\yourDatabase_logbackup.trn'

Remove file

alter database yourDatabase remove file secondary_logfile_logical_name;

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-file-and-filegroup-options?view=sql-server-ver16#syntax

if you get the error message 'The file secondary_logfile_logical_name cannot be removed because it's not empty' repeat the step 1, however if there is an active transaction using this log file it cannot be removed.