sql-server - SQL Server 数据库有 2 个日志文件，我想删除其中一个。如何？

Question

我对此很陌生。我有一个数据库(由其他人创建)，其中有 2 个 .ldf 文件。 (blah_log.ldf 和 blah_log2.ldf)。我的经理要求我删除其中一个日志文件，但我不能。我该怎么做呢？我尝试将其放在另一台服务器上，分离，删除日志文件，附加，但它给出了错误。我以为这样它只会创建一个，但它想要两个。然后我尝试右键单击属性并删除文件，不让我删除。它说日志文件不为空。我到底如何实现这一目标。我只是想让该数据库有一个该死的日志文件而不是两个。这不应该这么复杂。我是一个初学者，什么都不知道，所以也许事实并非如此。请帮忙!

我刚刚尝试过这个:空 SQL Server 数据库事务日志文件

使用 truncate_only 备份日志 [dbname]去DBCC SHRINKDATABASE([数据库名称]，10，TRUNCATEONLY)去

然后我删除了第二个日志文件并单击“确定”。我想这就是我需要做的一切？我在测试服务器上通过恢复尝试了它。

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