SQL Server database has 2 log files and I want to remove one. HOW?

Question

I am new to this. I have a database (created by someone else) that has 2 .ldf files. (blah_log.ldf and blah_log2.ldf). My manager asked me to remove one of the log files but I cannot. How do I do this? I tried to put it on another server, detach, delete log files, attach, but it gives an error. I thought that way it would create just one, but it wanted both. Then i tried to right click properties and delete the files, would not let me delete. It said the log file was not empty. How in the heck do I achieve this. I just want to make it where the dang database has one freaking log file not two. This shouldn't be this complicated. I am a beginner and know nothing so maybe it isn't really. Please HELP!

I just tried this: empty SQL Server database transaction log file

backup log [dbname] with truncate_only go DBCC SHRINKDATABASE ([dbname], 10, TRUNCATEONLY) go

Then I deleted the second log file and clicked ok. I guess this is all I need to do? I tried it on a test server from a restore.

Answer 1

This MSDN article describes how to accomplish this at a high-level:

You cannot move transaction log data from one log file to another to empty a transaction log file. To remove inactive transactions from a transaction log file, the transaction log must be truncated or backed up. When the transaction log file no longer contains any active or inactive transactions, the log file can be removed from the database.

And this blog post shows the actual T-SQL that will accomplish this task:

USE master 
IF DB_ID('rDb') IS NOT NULL DROP DATABASE rDb 
GO 

CREATE DATABASE rDb 
ON  
PRIMARY  
 ( NAME = N'rDb', FILENAME = N'C:\rDb.mdf' , SIZE = 50MB , 
     FILEGROWTH = 1024KB ) 
LOG ON  
 (NAME = N'rDb_log2', FILENAME = N'C:\rDb_log2.ldf', SIZE = 3MB, 
     FILEGROWTH = 2MB) 
,(NAME = N'rDb_log3', FILENAME = N'C:\rDb_log3.ldf', SIZE = 3MB, 
     FILEGROWTH = 2MB) 
,(NAME = N'rDb_log4', FILENAME = N'C:\rDb_log4.ldf', SIZE = 3MB, 
     FILEGROWTH = 2MB) 
GO 

ALTER DATABASE rDb SET RECOVERY FULL 
BACKUP DATABASE rDb TO DISK = 'C:\rDb.bak' WITH INIT 
CREATE TABLE rDb..t(c1 INT IDENTITY, c2 CHAR(100)) 

INSERT INTO rDb..t 
SELECT TOP(15000) 'hello' 
FROM syscolumns AS a 
  CROSS JOIN syscolumns AS b 

--Log is now about 46% full 
DBCC SQLPERF(logspace) 

--Check virtual log file layout 
DBCC LOGINFO(rDb) 
--See that file 4 isn't used at all (Status = 0 for all 4's rows) 

--We can remove file 4, it isn't used 
ALTER DATABASE rDb REMOVE FILE rDb_log4 

--Check virtual log file layout 
DBCC LOGINFO(rDb) 

--Can't remove 3 since it is in use 
ALTER DATABASE rDb REMOVE FILE rDb_log3 

--What if we backup log? 
BACKUP LOG rDb TO DISK = 'C:\rDb.bak' 

--Check virtual log file layout 
DBCC LOGINFO(rDb) 
--3 is still in use (status = 2) 

--Can't remove 3 since it is in use 
ALTER DATABASE rDb REMOVE FILE rDb_log3 

--Shrink 3 
USE rDb 
DBCC SHRINKFILE(rDb_log3) 
USE master 

--... and backup log? 
BACKUP LOG rDb TO DISK = 'C:\rDb.bak' 

--Check virtual log file layout 
DBCC LOGINFO(rDb) 
--3 is no longer in use 

--Can now remove 3 since it is not in use 
ALTER DATABASE rDb REMOVE FILE rDb_log3 

--Check explorer, we're down to 1 log file 

--See what sys.database_files say? 
SELECT * FROM rDb.sys.database_files 
--Seems physical file is gone, but SQL Server consider the file offline 

--Backup log does it: 
BACKUP LOG rDb TO DISK = 'C:\rDb.bak' 
SELECT * FROM rDb.sys.database_files 

--Can never remove the first ("primary") log file 
ALTER DATABASE rDb REMOVE FILE rDb_log2 
--Note error message from above