SQL Server 如何删除多余的事务日志文件

sqlserver按照严格顺序写入日志文件，如果有两个日志文件，sqlserver只会写满一个之后再写另一个，因此多个日志文件对性能提升并没有什么用处。如果创建了多个日志文件想要删除应该如何处理？

下面以full模式为例，sample模式更加简单

CREATE DATABASE [TestDB]
 ON  PRIMARY 
( NAME = N'TestDB', FILENAME = N'D:\SQL DATA\TestDB.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'TestDB_log1', FILENAME = N'D:\SQL DATA\TestDB_log1.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%), 
( NAME = N'TestDB_log2', FILENAME = N'D:\SQL DATA\TestDB_log2.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

USE TestDB
GO

CREATE TABLE TestTable
(
	ID INT IDENTITY(1,1),
	Value BIGINT
)

--Change recovery model for TestDB to FULL
ALTER DATABASE TestDB SET RECOVERY FULL;

插入一些测试数据

USE TestDB
GO

--Checking log information before insertion
SELECT file_id, name, type_desc, physical_name, size, max_size FROM sys.database_files

--Inserting data into TestTable
;WITH ValueTable AS
(
	SELECT 1 n
	UNION ALL 
	SELECT n+ 1
	FROM ValueTable
	WHERE n < 10000 --Value 10000 is used to facilitate testing process, please be careful in choosing this value for your server to avoid overloading it
) 
INSERT INTO TestTable (Value)
SELECT n
FROM ValueTable 
OPTION (MAXRECURSION 0)

--Checking log information after insertion
SELECT file_id, name, type_desc, physical_name, size, max_size FROM sys.database_files

查看日志信息及status

DBCC LOGINFO('TestDB')

可以看到fileid=2和3的文件都有部分VLF status变成了2，此时如果直接删除，会遇到报错

USE master
GO
--Remove TestDB_log2 file
ALTER DATABASE TestDB REMOVE FILE TestDB_log2

对于simple模式，等待checkpoint自动截断或手动收缩日志后，status会变成0，再次删除即可。

对于full模式，则需要发起日志备份（若未进行过全备还需要进行全备）。

--Full backup
BACKUP DATABASE TestDB TO DISK =N'D:\SQL DATA\TestDB.bak'
--Transaction log backup
BACKUP LOG TestDB TO DISK =N'D:\SQL DATA\TestDB.trn'

再次查看日志信息及status

DBCC LOGINFO('TestDB')

此时可以删除fileid=3的文件，注意对应的逻辑名是TestDB_log2

--Remove TestDB_log2 file
ALTER DATABASE TestDB REMOVE FILE TestDB_log2

此时查看dbfile信息，会发现TestDB_log2还在

--Checking log information
SELECT file_id, name, type_desc, physical_name, size, max_size FROM sys.database_files	

再进行一次日志备份，然后会发现TestDB_log2已经被删除了

--Transaction log backup
BACKUP LOG TestDB TO DISK =N'D:\SQL DATA\TestDB.trn'

--Checking log information
SELECT file_id, name, type_desc, physical_name, size, max_size FROM sys.database_files;