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;