The SQL Server database has 2 log files, and I want to delete them. AS?

Question

I am new to this. I have a database (created by someone else) that has 2 .ldf files in it. (blah_log.ldf and blah_log2.ldf). My manager asked me to delete one of the log files, but I can’t. How can I do it? I tried to put it on another server, disconnect, delete the log files, attach, but it gives an error. I thought this would create only one, but he wanted both. Then I tried to right-click and delete the files, would not let me delete. He said the log file is not empty. How the hell am I doing this? I just want to do this where the dang database contains one log file without two files. It should not be so difficult. I am new and don’t know anything, so maybe this is not so. Please help!

I just tried: 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. Think that's all I need? I tried this on a test server from recovery.

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 empty transaction log file. To remove inactive transactions from a transaction log file, the transaction log must be truncated or kept up. If the transaction log file no longer contains active or inactive transactions, you can delete the log file 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 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