即使在将日志备份到磁盘后，日志文件上的 DBCC SHRINKFILE 也不会减小大小

Question

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

我想尽可能缩小日志文件，因此我开始寻找如何做到这一点的方法。警告：我不是 DBA（甚至不是即将成为 DBA），并且一直凭感觉完成此任务。

首先，我进入 SSMS、数据库属性、文件，并将初始大小 (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 命令的输出中标识的最小大小

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

）和第一圈一样。我只能将日志文件减小到 62 GB。

我不确定我做错了什么以及下一步应该尝试什么。

Answer 1

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

在数据库中，使用以下查询找到日志文件的 file_id。

SELECT * FROM sys.database_files;

在我的实例中，日志文件是 file_id 2。现在我们要找到正在使用的虚拟日志，并使用以下命令执行此操作。

DBCC LOGINFO;

在这里，您可以通过查看状态是否为 2（正在使用）或 0（空闲）来了解是否有任何虚拟日志正在使用。收缩文件时，空的虚拟日志将从文件末尾开始物理删除，直到达到首次使用状态。这就是为什么收缩事务日志文件有时会部分收缩，但不会删除所有空闲虚拟日志。

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

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

然后，这将显示虚拟日志文件分配，希望您会注意到它有所减少。由于虚拟日志文件并不总是按顺序分配，您可能需要备份事务日志几次并再次运行最后一个查询；但我通常可以将其缩小到一两个备份之内。

Answer 2

除了已经采取的步骤之外，您还需要将恢复模式设置为简单，然后才能收缩日​​志。

对于生产系统这不是推荐的做法...您将失去从以前的备份/日志文件恢复到某个时间点的能力。

请参阅此 DBCC SHRINKFILE (Transact-SQL) msdn 页面上的示例 B一个例子和解释。

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) 

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

Answer 5

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

Answer 6

我尝试了很多方法，但这有效。

示例代码可在 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

Answer 7

我通过进行完整备份和事务备份解决了这个问题。有时，备份过程未完成，这就是 .ldf 文件未缩小的原因之一。试试这个。这对我有用。

Answer 8

我们的一个事务处理量很大的数据库每天都会在日志表中增加数十万条记录。有多个日志文件每天都会增长数百 GB。

我们有一个计划作业，每半小时进行一次差异备份。我们还有另一项预定的家政工作，每天清晨进行。

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

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

差异备份作业：

1. 仅当状态为“内务管理已完成”
2. 进行差异备份

这需要一段时间才能完成，但它可以在早上正常业务开始之前使我们的数据库变得整洁和新鲜。它对我们来说一直运行良好。

Answer 9

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

它确实有帮助，我已经成功地缩小了 tempdb 的数据文件。

Answer 10

我在数据库中遇到了这个问题，最后找到了关于 CHECKPOINT 调用的注释。我的数据库已经设置为简单日志记录。关键是要运行几次，直到有结果。我能够将大约 7GB 的日志文件减少到大约 100 MB。拥有内存优化的文件组可能与需要这样做的原因有关。

CHECKPOINT
GO
DBCC SHRINKFILE (Your_Log_File_Name_Or_Id_Here, 100)
GO

Answer 11

感谢@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