SQL Server 2008 日志不会被截断

SQL Server 2008 log will not truncate

提问by Simon Hughes

I consider myself a very experienced SQL person. But I'm failing to do these two things:

我认为自己是一个非常有经验的 SQL 人。但我没有做这两件事：

• Reduce the size of the allocated log.

• Truncate the log.

  DBCC sqlperf(logspace)

• 减少分配日志的大小。

• 截断日志。

  DBCC sqlperf（日志空间）

returns:

返回：

Database Name   Log Size (MB)   Log Space Used (%)  Status
ByBox       1964.25     30.0657         0

The following does not work with SQL 2008

以下不适用于 SQL 2008

DUMP TRANSACTION ByBox WITH TRUNCATE_ONLY

Running the following does nothing either

运行以下也没有任何作用

DBCC SHRINKFILE ('ByBox_1_Log' , 1)
DBCC shrinkdatabase(N'bybox')

I've tried backups. I've also tried setting the properties of the database - 'Recover Model' to both 'FULL' and 'SIMPLE' and a combination of all of the above. I also tried setting the compatibility to SQL Server 2005 (I use this setting as I want to match our production server) and SQL Server 2008.

我试过备份。我还尝试将数据库的属性 - 'Recover Model' 设置为 'FULL' 和 'SIMPLE' 以及上述所有属性的组合。我还尝试将兼容性设置为 SQL Server 2005（我使用此设置是因为我想匹配我们的生产服务器）和 SQL Server 2008。

No matter what I try, the log remains at 1964.25 MB, with 30% used, which is still growing.

无论我尝试什么，日志都保持在 1964.25 MB，使用了 30%，而且还在增长。

I'd like the log to go back down near 0% and reduce the log file size to, say, 100 MB which is plenty. My database must hate me; it just ignores everything I ask it to do regarding the log.

我希望日志回落到接近 0%，并将日志文件大小减少到 100 MB，这已经足够了。我的数据库一定恨我；它只是忽略了我要求它做的关于日志的所有事情。

One further note. The production database has quite a few replicated tables, which I turn off when I perform a restore on my development box by using the following:

进一步说明。生产数据库有相当多的复制表，当我使用以下命令在我的开发盒上执行还原时，我将其关闭：

-- Clear out pending replication stuff
exec sp_removedbreplication
go
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL,
     @numtrans = 0, @time = 0, @reset = 1
go

Trying:

试：

SELECT log_reuse_wait, log_reuse_wait_desc
FROM sys.databases
WHERE NAME='bybox'

Returns

退货

log_reuse_wait  log_reuse_wait_desc
0   NOTHING

How can I fix this problem?

我该如何解决这个问题？

---

---

Looking at thisand setting the recovery model to FULL I have tried the following:

查看此并将恢复模型设置为 FULL 我尝试了以下操作：

USE master
GO

EXEC sp_addumpdevice 'disk', 'ByBoxData', N'C:\<path here>\bybox.bak'

-- Create a logical backup device, ByBoxLog.
EXEC sp_addumpdevice 'disk', 'ByBoxLog', N'C:\<path here>\bybox_log.bak'

-- Back up the full bybox database.
BACKUP DATABASE bybox TO ByBoxData

-- Back up the bybox log.
BACKUP LOG bybox TO ByBoxLog

which returned:

返回：

Processed 151800 pages for database 'bybox', file 'ByBox_Data' on file 3.
Processed 12256 pages for database 'bybox', file 'ByBox_Secondary' on file 3.
Processed 1 pages for database 'bybox', file 'ByBox_1_Log' on file 3.
BACKUP DATABASE successfully processed 164057 pages in 35.456 seconds (36.148 MB/sec).

Processed 2 pages for database 'bybox', file 'ByBox_1_Log' on file 4.
BACKUP LOG successfully processed 2 pages in 0.056 seconds (0.252 MB/sec).

Perfect! But it's not.

完美的！但事实并非如此。

And DBCC SHRINKFILE ('ByBox_1_Log' , 1) now returns with

并且 DBCC SHRINKFILE ('ByBox_1_Log' , 1) 现在返回

DbId    FileId  CurrentSize MinimumSize UsedPages   EstimatedPages
7   2   251425  251425  251424  251424

and DBCC SQLPERF(LOGSPACE) still reports 30% usage.

和 DBCC SQLPERF(LOGSPACE) 仍然报告 30% 的使用率。

I think I may have to resign myself to the fact there could well be a bug in SQL Server 2008, or that my log file has been corrupted in some manner. However, my database is in good working order, which leads me to think there is a bug (shudders at the thought).

我想我可能不得不接受这样一个事实：SQL Server 2008 中很可能存在错误，或者我的日志文件已以某种方式损坏。但是，我的数据库处于良好的工作状态，这使我认为存在错误（想到时不寒而栗）。

采纳答案by Simon Hughes

Found the solution!

找到了解决办法！

I added a load of data to the database, so the log was forced to expand. I then removed the uneeded data to get my database back to how it was.

我向数据库添加了大量数据，因此日志被迫扩展。然后我删除了不需要的数据，让我的数据库恢复原状。

Backup and voila, a perfect 0% log.

备份和瞧，完美的 0% 日志。

So the solution is to make the log expand.

所以解决办法就是让日志展开。

回答by John

In my situation, I had a 650 MB database with a 370 GB log file in SQL Server 2008. No matter what I tried, I could not get it to shrink down. I tried everything listed as answers here but still, nothing worked.

在我的情况下，我在 SQL Server 2008 中有一个 650 MB 的数据库和一个 370 GB 的日志文件。无论我尝试什么，我都无法让它缩小。我尝试了此处列出的所有答案，但仍然没有任何效果。

Finally, I found a very short comment somewhere else that did work. It is to run this:

最后，我在其他地方找到了一条很短的评论，但确实有效。这是运行这个：

BACKUP LOG DatabaseName TO DISK = N'D:\Backup\DatabaseName_log.bak'
GO
DBCC SHRINKFILE('MyDatabase_Log', 1)
GO

This caused the log file to shrink from 37 GB down to 1 MB. Whew!

这导致日志文件从 37 GB 缩小到 1 MB。哇！

回答by Simon Hughes

I found DBCC SHRINKFILE (Transact-SQL)(MSDN).

我找到了DBCC SHRINKFILE (Transact-SQL)(MSDN)。

The following example shrinks the log file in the AdventureWorks database to 1 MB. To allow the DBCC SHRINKFILEcommand to shrink the file, the file is first truncated by setting the database recovery model to SIMPLE.

以下示例将 AdventureWorks 数据库中的日志文件缩小到 1 MB。为了允许DBCC SHRINKFILE命令缩小文件，首先通过将数据库恢复模型设置为 SIMPLE 来截断文件。

USE AdventureWorks;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks
SET RECOVERY FULL;
GO

回答by Raul

Beware of the implications of changing recovery models!!

当心改变恢复模型的影响！！

And now for one more sobering thought for all you production DBAsthinking about using the script:

现在，对于所有考虑使用该脚本的生产DBA 来说，还有一个更发人深省的想法：

BEFORE YOU CHANGE THE RECOVERY MODEL FROM FULL TO SIMPLE... there's no worries if you're in a development/QAenvironment. But if you're in a production environment where you're responsible to ensure full recovery of data in the event of an issue, you may want to take a closer look at what BOL says regarding doing this (see BOL under: "Managing Databases" -> "Transaction Log Management" -> "Recovery Models and Transaction Log Management"):

在您将恢复模型从完整更改为简单之前……如果您处于开发/ QA环境中，则无需担心。但是，如果您处于生产环境中，您有责任确保在出现问题时完全恢复数据，您可能需要仔细查看 BOL 对此的说明（请参阅 BOL 下的：“管理数据库” " -> "事务日志管理" -> "恢复模型和事务日志管理"):

A database can be switched to another recovery model at any time. However, switching from the simple recovery model, is unusual. Be aware that if you switch to the full recovery model during a bulk operation, the logging of the bulk operation changes from minimal logging to full logging, and vice versa.

一个数据库可以随时切换到另一个恢复模式。但是，从简单恢复模式切换是不寻常的。请注意，如果在批量操作期间切换到完整恢复模式，批量操作的日志记录将从最小日志记录更改为完整日志记录，反之亦然。

After switching from the simple recovery model

从简单恢复模式切换后

If you switch from the full or bulk-logged recovery model to the simple recovery model, you break the backup log chain. Therefore, we strongly recommend that you back up the log immediately before switching, which allows you to recover the database up to that point. After switching, you need to take periodic data backups to protect your data and to truncate the inactive portion of the transaction log.

After switching to the simple recovery model

If you switch from the full or bulk-logged recovery model to the simple recovery model, you break the backup log chain. Therefore, we strongly recommend that you back up the log immediately before switching, which allows you to recover the database up to that point.After switching, you need to take periodic data backups to protect your data and to truncate the inactive portion of the transaction log.

如果您从完整或大容量日志恢复模式切换到简单恢复模式，则会破坏备份日志链。因此，我们强烈建议您在切换前立即备份日志，这样您就可以将数据库恢复到该点。切换后，您需要定期进行数据备份以保护您的数据并截断事务日志的非活动部分。

切换到简单恢复模式后

如果您从完整或大容量日志恢复模式切换到简单恢复模式，则会破坏备份日志链。因此，我们强烈建议您在切换前立即备份日志，这样您就可以将数据库恢复到该点。切换后，您需要定期进行数据备份以保护您的数据并截断事务日志的非活动部分。

Really? "We strongly recommend that you back up the log immediately before switching, which allows you to recover the database up to that point." I cannot understand why this littletidbit of information is hidden in a section named "After switching to the simple recovery model" making most "normal people" think they can go ahead and switch it and then continue or come back and read this after changing it.

真的吗？“我们强烈建议您在切换前立即备份日志，这样您就可以将数据库恢复到那个点。” 我不明白为什么在名为“切换到简单恢复模式后”的部分中隐藏了这一小信息” 让大多数“普通人”认为他们可以继续并切换它，然后继续或在更改后回来阅读此内容。

Rant

咆哮

To Microsoft: So, please correct me if I'm wrong, if I fail to do the t-log backup BEFORE changing from FULL to SIMPLE and lo and behold my database gets corrupted somehow (ever heard of Murphy's law?) right before I'm able to take a backup... then I'm screwed, right? If switching the recovery model of my ****production**** database from FULL to SIMPLE is something that can break the backup log chain such that if I fail to take a transaction log backup before doing it (like it suggests above) I'm potentially going to lose data, then WHY THE HECK AREN'T YOU HIGHLIGHTING THAT IN A BLINKING MARQUEE MAKING IT A BIGGER DEAL than you seem to be??! You should literally be grabbing me by the shirt and slaping me to get my attention (so to speak) and warning me of the importance of this UPFRONT!!

致微软：所以，如果我错了，请纠正我，如果我在从 FULL 更改为 SIMPLE 之前没有做 t-log 备份，瞧瞧我的数据库不知何故被破坏了（听说过墨菲定律？）就在我之前我可以备份……那我就完蛋了，对吧？如果将我的 ****production**** 数据库的恢复模式从 FULL 切换到 SIMPLE 会破坏备份日志链，这样如果我在做之前没有进行事务日志备份（就像上面建议的那样）我可能会丢失数据，那你为什么不在一个闪烁的字幕中突出显示它比你看起来更重要？？！你真的应该抓住我的衬衫并扇我耳光以引起我的注意（可以这么说）并警告我这个UPFRONT的重要性！！

回答by Cedric FERNANDEZ

Another easy way to reduce the size of the log file, is to:

另一种减小日志文件大小的简单方法是：

• Backup logs
• Full backup of the database
• Shrink the logs file
• Backup logs again
• Shrink the logs file again

• 备份日志
• 数据库的完整备份
• 收缩日志文件
• 再次备份日志
• 再次收缩日志文件

In this way you don't have to modify any database parameters and your logs file is 1 MB sized.

通过这种方式，您无需修改​​任何数据库参数，并且您的日志文件大小为 1 MB。

回答by HardCode

I've always hated the way SQL Server handles the physical shrinking of log files. Please note that I've always done this via Enterprise Manager/SQL Server Management Studio, but it seems that when you shrink/truncate the log file, the physical size of the log file will not reduce until after doing a full backup on the database's data file, and then backing up the log file again. I could never nail down the exact pattern, but you could try and see what the exact sequence is. However, it has always involved doing a full backup of the data file.

我一直讨厌 SQL Server 处理日志文件物理收缩的方式。请注意，我一直通过企业管理器/SQL Server Management Studio 完成此操作，但似乎当您缩小/截断日志文件时，日志文件的物理大小在对数据库进行完整备份后才会减小数据文件，然后再次备份日志文件。我永远无法确定确切的模式，但您可以尝试看看确切的顺序是什么。但是，它始终涉及对数据文件进行完整备份。

回答by Mitch Wheat

Try running

尝试跑步

DBCC OPENTRAN

to check if there are any open transactions.

检查是否有任何未结交易。

回答by Sandra

I finally found a solution for the logfile shrink problem. All of the previous options did not work for me and did not shrink the logfile to the required size. The solution I found is:

我终于找到了解决日志文件收缩问题的方法。以前的所有选项对我都不起作用，也没有将日志文件缩小到所需的大小。我找到的解决方案是：

• Backup your database
• Set the recovery mode to simple
• Detach the database with SQL Server Management Studio
• Delete the logfile
• Attach the database without the logfile. In the lower half of the "add screen" you get a line which says that the logfile is missing
• Click remove for this line and OK for the attach
• Set the recovery mode back to full

• 备份你的数据库
• 将恢复模式设置为简单
• 使用SQL Server Management Studio分离数据库
• 删除日志文件
• 附加不带日志文件的数据库。在“添加屏幕”的下半部分，您会看到一行表示缺少日志文件
• 单击此行的删除，然后单击确定附加
• 将恢复模式设置回完整

回答by CGK

This will sound kind of stupid, but I find that I have to perform two full database and log file backups to be able to shrink the database.

这听起来有点愚蠢，但我发现我必须执行两次完整的数据库和日志文件备份才能缩小数据库。

When using SQL Server Management Studio, after doing a full database backup followed by a full transaction log backup, the shrink files page shows plenty of free space available, but it will not truncate the log files.

使用SQL Server Management Studio 时，在执行完整数据库备份和完整事务日志备份后，收缩文件页面显示大量可用空间，但不会截断日志文件。

However, when I run a second full backup of both the database and the log file, I find that the full backup of the transaction log is much much smaller as it appears to only be backing up the new changes since the last backup.

但是，当我对数据库和日志文件运行第二次完整备份时，我发现事务日志的完整备份要小得多，因为它似乎只备份自上次备份以来的新更改。

Once this second backup is complete, I run the shrink tool again within the management studio. It still shows that there is plenty of available free space, but this time when I click OK, the log file reduces in size.

第二次备份完成后，我再次在管理工作室中运行收缩工具。它仍然显示有足够的​​可用空间，但是这次当我单击“确定”时，日志文件的大小会减小。

So, try the following.

因此，请尝试以下操作。

1. Take a full backup of both the database and the log file. If you check within the shrink tool, you should see that there is now plenty of available free space in the log file. However clicking OK won't remove the free space.

2. Take a second full backup of both the database and the log file. You should find the full backup of the database is similar in size to the first full backup. The size of the full transaction log backup should be much smaller.

3. Run the shrink tool on the log file and with any luck, the log file should reduce in size. The last time I did this, it reduced from 180 GB to 12 MB and the shrink tool states that there is still 10 MB of available free space within the file.

1. 对数据库和日志文件进行完整备份。如果您检查收缩工具，您应该看到日志文件中现在有足够的可用空间。但是，单击“确定”不会删除可用空间。

2. 对数据库和日志文件进行第二次完整备份。您应该会发现数据库的完整备份与第一次完整备份的大小相似。完整事务日志备份的大小应该小得多。

3. 在日志文件上运行收缩工具，如果幸运的话，日志文件的大小应该会减小。我上次这样做时，它从 180 GB 减少到 12 MB，并且收缩工具指出文件中仍有 10 MB 的可用空间。

回答by Julien Chappel

Reading the answers I hardly believe that they are written by DBAs. Basic golden rules:

阅读这些答案时，我几乎不相信它们是由 DBA 编写的。基本黄金法则：

1. In any database before you performing any maintenance, including shrinking the log, you should perform a full backup.

2. Carry out any database maintenance, including shrinking, when nothing else is happening on that particular database for the whole duration of the maintenance. If it is necessary, suspend non-essencial applications. Always keep it in mind that the healthy database is the soul of any applications interacting with it.

1. 在执行任何维护（包括缩小日志）之前，在任何数据库中，都应该执行完整备份。

2. 如果在整个维护期间该特定数据库上没有发生任何其他事情，则执行任何数据库维护，包括收缩。如果有必要，暂停非必要的应用程序。永远记住，健康的数据库是与之交互的任何应用程序的灵魂。

After all this, the following commands for shrinking the database's transaction log always worked fine with me on SQL Server 2005 and later SQL Server versions:

毕竟，以下用于缩小数据库事务日志的命令在 SQL Server 2005 和更高版本的 SQL Server 上始终适用于我：

USE DatabaseName
GO
-- Truncate the Transaction log
ALTER DATABASE DatabaseName SET RECOVERY SIMPLE
CHECKPOINT
ALTER DATABASE DatabaseName SET RECOVERY FULL
GO
-- Shrink the Transaction Log as recommended my Microsoft.

DBCC SHRINKFILE ('database_txlogfilelogicalname', [n -size to shrink in MBytes])
GO
 -- Pass the freed pages back to OS control.
DBCC SHRINKDATABASE (DatabaseName, TRUNCATEONLY)
GO
-- Tidy up the pages after shrink
DBCC UPDATEUSAGE (0);
GO
-- IF Required but not essential
-- Force to update all tables statistics
exec sp_updatestats
GO