Say you happened to get too many transaction log (ldf) files. Can you remove log files from the database? Yes, but only if a file isn’t in use, and you cannot remove the first (“primary”) log file.
So, be prepared to investigate the virtual file layout, using DBCC LOGINFO, to see if a log file is in use or not. You can find information about how to investigate the virtual log file layout in my shrink article. The basic steps are a bit similar to shrinking a log file: Investigate virtual log file layout, backup log, possibly shrink file, try removing it. Do this again as many times as it takes (repeat, rinse and lather).
Below is a script that, if you take the time to study it and play with it, will prepare you to remove transaction log files from a database. As always, don’t execute it if you don’t understand what it does!
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