日志文件上的DBCC SHRINKFILE在备份日志到磁盘后仍未减小大小

Question

我有一个名为[My DB]的数据库，其中包含以下信息：
SQL Server 2008
MDF大小：30 GB
LDF大小：67 GB

我想尽可能地缩小日志文件，所以开始了解如何做到这一点。但需要注意的是，我不是DBA（甚至都不接近DBA），通过摸索来进行。

首先，我只是进入SSMS，DB属性，文件，然后编辑“初始大小（MB）”值为10。这将日志文件缩小到62 GB（并非我输入的确切10 MB）。因此，我附加了SQL Profiler，发现DBCC SHRINKFILE被调用。然后我在查询编辑器中输入了该命令，以下是结果。

DBCC SHRINKFILE (N'My DB_Log' , 10)

输出结果如下：

Cannot shrink log file 2 (My DB_Log) because the logical log file located at the end of the file is in use.
DbId   FileId      CurrentSize MinimumSize UsedPages   EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
8      2           8044104     12800       8044104     12800

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

我随后对此进行了一些研究，并找到了这个链接:

http://support.microsoft.com/kb/907511

其中说我需要在收缩文件之前备份日志文件，以释放虚拟日志文件并使收缩文件能够完成其工作 - 我不知道这是什么意思... 我只是转述一下 :)

所以，我决定尝试备份日志文件，然后执行 DBCC SHRINKFILE 命令（并将新日志文件大小更改为 12800，因为这是上一个 DBCC SHRINKFILE 命令输出中识别的 MinimumSize）

BACKUP LOG [My DB] TO DISK = 'D:\SQLBackup\20110824-MyDB-Log.bak'
GO
DBCC SHRINKFILE (N'My DB_Log' , 12800)
GO

结果与第一次操作相同。 我只能将日志文件缩小到62 GB。

我不确定我做错了什么，以及接下来应该尝试什么。

- Ed Sinek

---

为了防止这种情况再次发生，您应该运行日志备份或将恢复模式设置为简单模式。 - DForck42

我的问题是一个暂停的复制/镜像设置 - 看来如果SQL认为它们将需要用于复制，它就不会收缩tlogs。这可能不是很多人的问题，但它可能对一些人有帮助。 - pettys

Answer 1

好的，这里有一个解决方案可以减小事务日志文件的物理大小，但是不需要将恢复模式更改为简单模式。

在你的数据库中，使用以下查询定位日志文件的file_id。

SELECT * FROM sys.database_files;

在我的情况下，日志文件是文件ID为2。现在我们想要定位正在使用的虚拟日志，并使用以下命令完成此操作。

DBCC LOGINFO;

您可以查看状态是否为2（正在使用）或0（空闲）来确定是否使用了任何虚拟日志。收缩文件时，从文件末尾开始物理删除空的虚拟日志，直到遇到第一个已使用的状态。这就是为什么收缩事务日志文件有时会部分收缩但不会删除所有空闲虚拟日志的原因。

如果您注意到0之后出现了状态2，这会阻止文件完全收缩。为了解决这个问题，请执行另一个事务日志备份，并立即运行这些命令，提供上面找到的file_id和您想要缩小日志文件的大小。

-- DBCC SHRINKFILE (file_id, LogSize_MB)
DBCC SHRINKFILE (2, 100);
DBCC LOGINFO;

这将显示虚拟日志文件的分配情况，希望您会注意到已经有所减少。由于虚拟日志文件不总是按顺序分配的，您可能需要备份交易日志几次，然后再运行此最后一个查询; 但通常我可以在一两个备份中将其缩小。

- Radderz

---

表现得非常出色。备份99%的空闲日志只需要一秒钟，文件立即从超过22,000MB减少到200MB。这应该标记为正确答案。 - Triynko

我很感激您提供的背景信息。但是，我仍然卡住了，因为我认为您的解决方案归结为原始帖子中尝试过但无法正常工作的解决方案。您的解决方案基本上是运行“BACKUP LOG... GO DBCC SHRINKFILE...”多次直到成功吗？我已经尝试了几次，但没有成功，而且似乎OP也尝试了但没有成功。只是想澄清一下，看看您的答案中是否有我遗漏的内容。 - pettys

更新：我的问题是因为一个暂停的复制/镜像设置正在锁定tlog。可能是一个小的边缘情况。 - pettys

如果你想将文件缩小到最小的尺寸，那么你需要一次性备份、缩小、备份和再次缩小。我认为这是有意为之的，因为缩小只会将日志文件的大小缩小到自上次备份以来的“已使用页面”的大小；这样可以最大程度地减少增加日志文件的需要，对于经常进行负载和备份的生产环境非常有用。以上内容旨在演示和调试此过程。如果你的日志文件中有“0”，那么它可以进一步缩小。如果没有，“释放备份日志页”的问题可能会导致问题。 - Radderz

Answer 2

除了你已经采取的步骤，你还需要在收缩日志之前将恢复模式设置为简单模式。

这不是生产系统推荐的做法... 这样会导致无法从以前的备份/日志文件中恢复到某个时间点。

请参考此DBCC SHRINKFILE (Transact-SQL) msdn页面上的示例B，并进行解释。

- jlnorsworthy

---

只是出于好奇，是否有一种方法可以通过 SSMS UI 查看恢复模式？我查看了数据库属性但没有看到它。最接近的是显示恢复/页面验证值的“属性/选项”页面。 - Ed Sinek

找到了 - 它不在“其他选项”列表中。它在页面顶部 - 三个下拉菜单之一。谢谢。 - Ed Sinek

我本来想建议运行这个查询：select recovery_model_desc, from sys.databases where name = '数据库名称' - jlnorsworthy

谢谢。修改恢复模式是关键 :) - Alireza

Answer 3

试试这个

ALTER DATABASE XXXX  SET RECOVERY SIMPLE

use XXXX

declare @log_File_Name varchar(200) 

select @log_File_Name  = name from sysfiles where filename like '%LDF'

declare @i int = FILE_IDEX ( @log_File_Name)

dbcc shrinkfile ( @i , 50) 

- user2630576

---

这真是太棒了！ - paul-2011

Answer 4

我在 SQL Server 2008 R2 上使用这个脚本。

USE [db_name]

ALTER DATABASE [db_name] SET RECOVERY SIMPLE WITH NO_WAIT

DBCC SHRINKFILE([log_file_name]/log_file_number, wanted_size)

ALTER DATABASE [db_name] SET RECOVERY FULL WITH NO_WAIT

- manit

---

如果你这样做的话，你就会破坏事务日志备份链。因此，如果在你恢复到完全恢复模式之后发生灾难，除非你在重置为完全恢复之后启动完整或差异备份，否则你将百分之百丢失数据。另外，“WITH NO_WAIT”意味着你不给正在执行的事务完成的时间。更好的选择是“WITH ROLLBACK AFTER 10 SECONDS”（或类似的选项）。 - Michael K. Campbell

如何获取日志文件名或编号？ - Kiquenet

Answer 5

我尝试了很多方法，但这个可行。

示例代码可在DBCC SHRINKFILE中找到。

USE DBName;  
GO  
-- Truncate the log by changing the database recovery model to SIMPLE.  
ALTER DATABASE DBName  
SET RECOVERY SIMPLE;  
GO  
-- Shrink the truncated log file to 1 MB.  
DBCC SHRINKFILE (DBName_log, 1);  --File name SELECT * FROM sys.database_files; query to get the file name
GO  
-- Reset the database recovery model.  
ALTER DATABASE DBName  
SET RECOVERY FULL;  
GO

- Sathish

---

这是一个非常糟糕的想法，它在当前接受的答案中提供，并附有免责声明，说明为什么它是糟糕的！ - James Jenkins

Answer 6

Paul Randal在他的博客中对这个问题进行了精彩的讨论：http://www.sqlskills.com/blogs/paul/post/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to-stop-it.aspx

- PseudoToad

---

备份日志 ETL_Log_1 WITH NO_LOG 不起作用。 - Kiquenet

Answer 7

我通过进行完整和事务备份来解决了这个问题。有时，备份过程未完成，这是 .ldf 文件无法收缩的原因之一。尝试这样做。这对我起作用了。

- swapnil gaikwad

Answer 8

我们的一些高交易量数据库每天都会在日志表中增加数十万条记录。每天有多个日志文件都会增长数百GB。

我们有一个定期作业，每半小时进行一次差异备份。我们还有另一个定期作业用于清理，每天早上运行。

我们在将RECOVERY设置为SIMPLE后，在清理期间执行SHRINKFILE操作。在过程开始和结束时，我们会进行完整备份，以克服从之前的备份/日志文件恢复到某个时间点的问题。我们在数据库中使用一些标记，以确保在清理作业完成之前不尝试进行差异备份。 简要概述如下 - 清理作业：

1. 将状态设置为“正在进行清理”
2. 将数据库设置为单用户模式
3. 对数据库进行完整备份
4. 从各种旧表中删除记录
5. 将数据库恢复模式设置为简单模式
6. 遍历日志文件并收缩每个日志文件
7. 将数据库恢复模式设置为完整模式
8. 对数据库进行完整备份
9. 将数据库设置为多用户模式
10. 将状态设置为“清理完成”

差异备份作业：

1. 仅在状态为“清理完成”时继续
2. 进行差异备份

它需要一段时间才能完成，但是它可以在常规业务在早上开始之前使我们的数据库整洁和新鲜。 它已经为我们正常工作了。

- Mujib Khan

Answer 9

遇到了同样的问题，尝试使用DBCC FREEPROCCACHE解决。

这确实有帮助，我成功地对tempdb的数据文件进行了收缩。

- marcin2x4

Answer 10

感谢 @user2630576 和 @Ed.S.

以下代码很好地解决了问题：

BACKUP LOG [database] TO DISK = 'D:\database.bak'
GO

ALTER DATABASE [database] SET RECOVERY SIMPLE

use [database]

declare @log_File_Name varchar(200)

select @log_File_Name = name from sysfiles where filename like '%LDF'

declare @i int = FILE_IDEX ( @log_File_Name)

dbcc shrinkfile ( @i , 50)

ALTER DATABASE [database] SET RECOVERY FULL

- Aussie Ash

---

这是一个非常糟糕的想法，它在目前接受的答案中提供，并附有免责声明，说明为什么它是糟糕的！ - James Jenkins