我不是SQL专家，每次我需要做一些超出基础知识的事情时，都会想起这个事实。 我有一个规模不大的测试数据库，但是事务日志肯定是这样。 如何清除交易日志？
对于遇到意外增长(您不希望再次发生这种情况)的情况，应该真正减少日志文件的大小。如果日志文件将再次增长到相同的大小，则暂时缩小将不会有太多效果。现在，根据数据库的恢复目标，这些是您应该执行的操作。
好。
首先，进行完整备份
如果发生问题，请务必对数据库进行任何更改，否则请确保无法还原数据库。
好。
如果您关心时间点恢复
(通过时间点恢复，我的意思是您关心的是能够还原到除完整备份或差异备份以外的任何内容。)
好。
大概您的数据库处于FULL恢复模式。如果没有，请确保它是：
好。
1
| ALTER DATABASE testdb SET RECOVERY FULL; |
即使您进行常规的完整备份，日志文件也会不断增长，直到执行日志备份为止-这是为了保护您，而不是不必要地吞噬了磁盘空间。根据恢复目标，您应该经常执行这些日志备份。例如，如果您有一条业务规则规定在发生灾难时可以承受不超过15分钟的数据丢失损失，那么您应该有一份每15分钟备份一次日志的作业。这是一个脚本，它将基于当前时间生成带有时间戳的文件名(但是您也可以使用维护计划等来执行此操作，只是不要在维护计划中选择任何收缩选项，它们太糟糕了)。
好。
1
2
3
4
5
6
| DECLARE @path NVARCHAR(255) = N'\\backup_share\log\testdb_'
+ CONVERT(CHAR(8), GETDATE(), 112) + '_'
+ REPLACE(CONVERT(CHAR(8), GETDATE(), 108),':','')
+ '.trn';
BACKUP LOG foo TO DISK = @path WITH INIT, COMPRESSION; |
请注意，\\backup_share\应该位于代表不同基础存储设备的另一台计算机上。将这些备份到同一台计算机(或使用同一基础磁盘的另一台计算机，或同一物理主机上的另一台VM)并不能真正帮助您，因为如果计算机崩溃，您将丢失数据库及其备份。根据您的网络基础架构，在本地备份然后将其转移到幕后的其他位置可能更有意义。无论哪种情况，您都希望尽快将它们从主数据库计算机中删除。
好。
现在，一旦运行了常规的日志备份，就应该将日志文件压缩到比现在炸毁的文件更合理的大小。这并不意味着一遍又一遍地运行SHRINKFILE，直到日志文件为1 MB-即使您频繁备份日志，它仍然需要容纳可能发生的所有并发事务的总和。日志文件自动增长事件非常昂贵，因为SQL Server必须将文件归零(与启用即时文件初始化后的数据文件不同)，并且用户事务必须等待这种情况发生。您希望尽可能少地执行此增长-收缩-增长-收缩例程，并且您当然不想让您的用户为此付费。
好。
请注意，您可能需要先备份两次日志，然后才能进行收缩(感谢Robert)。
好。
因此，您需要为日志文件提出一个实际的大小。没有人可以在不了解系统的情况下告诉您那是什么，但是如果您经常缩小日志文件并且又在增长，那么一个很好的水印可能比最大的水印高10-50％。 。假设这是200 MB，并且您希望任何后续的自动增长事件为50 MB，那么您可以通过以下方式调整日志文件的大小：
好。
1
2
3
4
5
6
| USE [master];
GO
ALTER DATABASE Test1
MODIFY FILE
(NAME = yourdb_log, SIZE = 200MB, FILEGROWTH = 50MB);
GO |
请注意，如果日志文件当前> 200 MB，则可能需要先运行此文件：
好。
1
2
3
4
| USE yourdb;
GO
DBCC SHRINKFILE(yourdb_log, 200);
GO |
如果您不关心时间点恢复
如果这是一个测试数据库，并且您不关心时间点恢复，则应确保数据库处于SIMPLE恢复模式。
好。
1
| ALTER DATABASE testdb SET RECOVERY SIMPLE; |
将数据库置于SIMPLE恢复模式将确保SQL Server重用日志文件的某些部分(实质上淘汰不活动的事务)，而不是为了保留所有事务的记录(如FULL恢复所做的直到您返回)日志)。 CHECKPOINT事件将有助于控制日志，并确保它不需要增长，除非您在CHECKPOINT之间生成了大量的t-log活动。
好。
接下来，您应绝对确保此日志增长确实是由于异常事件(例如，每年一次的春季大扫除或重建您的最大指标)，而不是由于正常的日常使用。如果将日志文件缩小到一个可笑的小尺寸，而SQL Server只需要再次增大它以适应您的正常活动，那么您获得了什么？您是否能够利用您只是暂时释放的磁盘空间？如果需要立即修复，则可以运行以下命令：
好。
1
2
3
4
5
6
7
8
| USE yourdb;
GO
CHECKPOINT;
GO
CHECKPOINT; -- run twice to ensure file wrap-around
GO
DBCC SHRINKFILE(yourdb_log, 200); -- unit is set in MBs
GO |
否则，请设置适当的大小和增长率。根据时间点恢复情况下的示例，您可以使用相同的代码和逻辑来确定合适的文件大小并设置合理的自动增长参数。
好。
一些你不想做的事
使用TRUNCATE_ONLY选项和SHRINKFILE备份日志。例如，此TRUNCATE_ONLY选项已被弃用，并且在SQL Server的当前版本中不再可用。其次，如果您使用的是FULL恢复模型，则这将破坏您的日志链，并需要新的完整备份。
好。
分离数据库，删除日志文件，然后重新附加。我不能强调这有多危险。您的数据库可能无法备份，可能会被怀疑备份，可能必须还原到备份(如果有备份)，等等。
好。
使用"缩小数据库"选项。 DBCC SHRINKDATABASE和维护计划选项执行相同的操作不是一个好主意，尤其是在您真的只需要解决日志问题的情况下。定位要调整的文件，并使用DBCC SHRINKFILE或ALTER DATABASE ... MODIFY FILE分别进行调整(上面的示例)。
好。
将日志文件缩小到1 MB。这看起来很诱人，因为，在某些情况下，SQL Server将允许我执行此操作，并查看它释放的所有空间！除非您的数据库是只读的(并且应该使用ALTER DATABASE标记它)，否则这绝对会导致许多不必要的增长事件，因为无论恢复模式如何，日志都必须容纳当前事务。暂时释放该空间的目的是什么，以便SQL Server可以缓慢而痛苦地收回该空间？
好。
创建第二个日志文件。这将暂时缓解已满磁盘的驱动器，但这就像试图用创可贴修复被刺破的肺部一样。您应该直接处理有问题的日志文件，而不是仅添加另一个潜在的问题。除了将某些事务日志活动重定向到其他驱动器之外，第二个日志文件确实对您没有任何作用(与第二个数据文件不同)，因为一次只能使用其中一个文件。保罗·兰德尔(Paul Randal)还解释了为什么以后会有多个日志文件咬你。
好。
好。
主动
与其将您的日志文件缩小到少量并让其以小幅度不断地自动增长，不如将其设置为一个相当大的大小(一个可以容纳您最大并发事务集的总和)并设置一个合理的自动增长设置为后备，这样它就不必多次增长就可以满足单个事务，因此在正常业务运营期间必须不断增长的情况相对很少。
好。
这里最糟糕的设置是1 MB增长或10％增长。有趣的是，这些是SQL Server的默认值(我抱怨过，要求更改无用)-数据文件1 MB，日志文件10％。前者在当今时代太小了，后者每次导致的事件越来越长(例如，您的日志文件为500 MB，第一个增长为50 MB，下一个增长为55 MB，下一个增长为60.5 MB等等。-在慢I / O上，相信我，您会真正注意到此曲线)。
好。
进一步阅读
请不要在这里停下来；尽管您看到的有关缩小日志文件的许多建议本质上都是不好的，甚至可能是灾难性的，但有些人更关心数据的完整性而不是释放磁盘空间。
好。
我在2009年撰写的博客文章中，出现了一些"缩小日志文件的方法"的文章。
好。
Brent Ozar在四年前写了一篇博客文章，指向多种资源，以回应《 SQL Server杂志》上本不应该发表的文章。
好。
保罗·兰德尔(Paul Randal)的博客文章解释了为什么维护日志很重要以及为什么不应该收缩数据文件。
好。
Mike Walsh也提供了涵盖这些方面的好答案，包括为什么您可能无法立即缩小日志文件的原因。
好。
好。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| -- DON'T FORGET TO BACKUP THE DB :D (Check [here][1])
USE AdventureWorks2008R2;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2008R2_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY FULL;
GO |
来自：DBCC SHRINKFILE(Transact-SQL)
您可能要先备份。
免责声明：请仔细阅读下面的评论，我认为您已经阅读了接受的答案。正如我近5年前所说：
if anyone has any comments to add for situations when this is NOT an
adequate or optimal solution then please comment below
-
右键单击数据库名称。
-
选择任务→收缩→数据库
-
然后单击OK！
我通常打开包含数据库文件的Windows资源管理器目录，因此可以立即看到效果。
实际上，我对此感到很惊讶！通常，我以前使用过DBCC，但是我只是尝试了一下，并且没有缩小任何内容，所以我尝试了GUI(2005)，它的效果很好-在10秒内释放了17GB的空间
在完全恢复模式下，这可能无法正常工作，因此您必须先备份日志，或者更改为简单恢复，然后收缩文件。 [为此感谢@onupdatecascade]
-
PS：我很高兴有人对这种做法的危害发表了评论，但是在我的环境中，我自己这样做没有任何问题，尤其是因为我总是首先进行完整备份。因此，在继续之前，请考虑您的环境是什么，以及这如何影响您的备份策略和作业安全性。我所做的只是向人们指出Microsoft提供的功能！
以下是用于缩小交易日志的脚本，但是我绝对建议您在缩小交易日志之前先备份它。
如果仅收缩文件，则将丢失大量数据，这些数据可能在发生灾难时起到救生作用。事务日志包含许多有用的数据，这些数据可以使用第三方事务日志读取器读取(可以手动读取，但需要付出很大的努力)。
对于时间点恢复，事务日志也是必须的，因此，不要只是丢弃它，而要确保事先备份它。
这是人们使用事务日志中存储的数据来完成恢复的一些帖子：
1
2
3
4
5
6
7
8
9
10
11
12
| USE DATABASE_NAME;
GO
ALTER DATABASE DATABASE_NAME
SET RECOVERY SIMPLE;
GO
--First parameter is log file name and second is size in MB
DBCC SHRINKFILE (DATABASE_NAME_Log, 1);
ALTER DATABASE DATABASE_NAME
SET RECOVERY FULL;
GO |
执行上面的命令时，您可能会收到类似以下的错误
"Cannot shrink log file (log file name) because the logical
log file located at the end of the file is in use"
这意味着正在使用TLOG。在这种情况下，请尝试连续执行几次，或者找到减少数据库活动的方法。
这是一种简单且非常微妙且潜在危险的方式。
备份数据库
分离数据库
重命名日志文件
附加数据库
新的日志文件将被重新创建
删除重命名的日志文件。
我猜您没有在做日志备份。 (截断日志)。我的建议是将恢复模型从完全更改为简单。这将防止日志膨胀。
如果您不使用事务日志进行还原(即，您只做过完整备份)，则可以将恢复模式设置为"简单"，事务日志很快就会缩水，再也不会填满。
如果使用的是SQL 7或2000，则可以在数据库选项选项卡中启用"在检查点上截断日志"。这具有相同的效果。
显然，在生产环境中不建议这样做，因为您将无法还原到某个时间点。
到目前为止，此处的大多数答案都是假设您实际上不需要事务日志文件，但是，如果您的数据库使用的是FULL恢复模型，并且您想要保留备份以防万一需要还原数据库，则不要截断或按照许多答案建议的方式删除日志文件。
删除日志文件(通过截断，丢弃，擦除等)将破坏您的备份链，并阻止您将其恢复到上次完整，差异或事务日志备份以来的任何时间点，直到下一次完整备份为止或进行差异备份。
摘自Microsoft关于BACKUP的文章
We recommend that you never use NO_LOG or TRUNCATE_ONLY to manually
truncate the transaction log, because this breaks the log chain. Until
the next full or differential database backup, the database is not
protected from media failure. Use manual log truncation in only very
special circumstances, and create backups of the data immediately.
为避免这种情况，请在缩小日志文件之前将其备份到磁盘。语法如下所示：
1
2
3
4
| BACKUP LOG MyDatabaseName
TO DISK='C:\DatabaseBackups\MyDatabaseName_backup_2013_01_31_095212_8797154.trn'
DBCC SHRINKFILE (N'MyDatabaseName_Log', 200) |
不推荐John推荐的这项技术，因为不能保证没有日志文件就可以附加数据库。将数据库从完全更改为简单，强制执行检查点并等待几分钟。 SQL Server将清除日志，然后可以使用DBCC SHRINKFILE缩小日志。
SQL Server事务日志需要适当维护，以防止其意外增长。这意味着经常运行事务日志备份。否则，您就有交易日志变满并开始增长的风险。
除了这个问题的答案之外，我还建议您阅读并理解事务日志的常见神话。这些读数可能有助于理解事务日志并决定使用哪些技术"清除"它：
来自10个最重要的SQL Server事务日志神话：
Myth: My SQL Server is too busy. I don’t want to make SQL Server transaction log backups
One of the biggest performance intensive operations in SQL Server is an auto-grow event of the online transaction log file. By not making transaction log backups often enough, the online transaction log will become full and will have to grow. The default growth size is 10%. The busier the database is, the quicker the online transaction log will grow if transaction log backups are not created
Creating a SQL Server transaction log backup doesn’t block the online transaction log, but an auto-growth event does. It can block all activity in the online transaction log
从交易日志神话：
Myth: Regular log shrinking is a good maintenance practice
假。日志增长非常昂贵，因为必须将新块清零。所有写入活动将在该数据库上停止，直到完成归零为止，并且如果磁盘写入速度慢或自动增长的大小很大，则暂停可能会很大，用户会注意到。这就是为什么要避免增长的原因之一。如果缩小日志，它将再次增长，并且您只是在不必要的缩小和增长游戏上浪费了磁盘操作
使用DBCC ShrinkFile ({logicalLogName}, TRUNCATEONLY)命令。如果这是一个测试数据库，而您正在尝试节省/回收空间，则将有所帮助。
请记住，尽管TX日志确实具有一定的最小/稳定状态大小，它们会逐渐增长。根据您的恢复模型，您可能无法缩小日志-如果为FULL，并且您不发行TX日志备份，则无法缩小日志-它将永远增长。如果不需要TX日志备份，请将恢复模式切换为"简单"。
请记住，在任何情况下都绝对不要删除日志(LDF)文件！您几乎会立即遇到数据库损坏。煮熟了！做完了！数据丢失！如果"未修复"，则主MDF文件可能会永久损坏。
永远不要删除事务日志-您将丢失数据！如果您分离并"重命名"有效删除部分数据库的TX日志文件，则部分数据位于TX日志中(与恢复模型无关)。
对于那些删除了TX日志的用户，您可能需要运行一些checkdb命令并修复损坏，然后再丢失更多数据。
查看Paul Randal的有关此主题的博客文章，不好的建议。
另外，通常不要在MDF文件上使用rinklefile，因为它会严重碎片化数据。查阅他的不良建议部分以获取更多信息("为什么不应该收缩数据文件")
查看Paul的网站-他涵盖了这些问题。上个月，他在"神话一天"系列中探讨了许多这些问题。
首先检查数据库恢复模型。默认情况下，SQL Server Express Edition创建用于简单恢复的数据库
型号(如果我没记错的话)。
带有Truncate_Only的备份日志DatabaseName：
1
| DBCC ShrinkFile(yourLogical_LogFileName, 50) |
SP_helpfile将为您提供逻辑日志文件名。
参考：
从SQL Server数据库中的完整事务日志中恢复
如果您的数据库处于完全恢复模型中，并且没有进行TL备份，则将其更改为SIMPLE。
根据我在大多数SQL Server上的经验，没有事务日志的备份。
完全备份或差异备份是常见的做法，但是事务日志备份确实很少。
因此，事务日志文件将永远增长(直到磁盘已满)。
在这种情况下，恢复模型应设置为"简单"。
不要忘记修改系统数据库" model"和" tempdb"。
数据库" tempdb"的备份没有意义，因此此数据库的恢复模型应始终为"简单"。
截断日志文件：
-
备份数据库
-
通过使用企业管理器或通过执行以下操作来分离数据库：Sp_DetachDB [DBName]
-
删除事务日志文件。 (或重命名文件，以防万一)
-
使用以下方法重新附加数据库：Sp_AttachDB [DBName]
-
附加数据库后，将创建一个新的事务日志文件。
缩小日志文件：
您可以通过运行sp_helpdb或在企业管理器中查找数据库的属性来找到日志文件的逻辑名。
备份MDB文件。
停止SQL服务
重命名日志文件
启动服务
(系统将创建一个新的日志文件。)
删除或移动重命名的日志文件。
尝试这个：
1
2
3
4
5
6
7
8
9
10
11
| USE DatabaseName
GO
DBCC SHRINKFILE( TransactionLogName, 1)
BACKUP LOG DatabaseName WITH TRUNCATE_ONLY
DBCC SHRINKFILE( TransactionLogName, 1)
GO |
数据库→右键单击属性→文件→添加另一个名称不同的日志文件，并将路径设置为与文件名称不同的旧日志文件相同。
数据库自动选择新创建的日志文件。
备份数据库
分离数据库
重命名日志文件
附加数据库(附加附加删除重命名的.ldf(日志文件)。选择它并按"删除"按钮删除)
新的日志文件将被重新创建
删除重命名的日志文件。
这将起作用，但是建议您首先备份数据库。
我发生的情况是数据库日志文件为28 GB。
您可以采取什么措施来减少这种情况？
实际上，日志文件是发生事务后SQL Server保留的那些文件数据。为了处理事务，SQL Server为其分配页面。但是，在交易完成后，这些交易不会突然被释放，希望可能会有同一笔交易。这占用了空间。
步骤1：
首先在探索的数据库查询中运行此命令
检查站
第2步：
右键单击数据库
任务>备份
选择备份类型作为事务日志
添加目标地址和文件名以保留备份数据(.bak)
再次重复此步骤，这时再指定一个文件名
第三步：
现在去数据库
右键单击数据库
任务>缩小>文件
选择文件类型作为日志
收缩动作，释放未使用的空间
步骤4：
检查您的日志文件
通常在SQL 2014中可以在以下位置找到
C： Program Files Microsoft SQL Server MSSQL12.MSSQL2014EXPRESS MSSQL DATA
就我而言，它从28 GB减少到1 MB
例：
1
2
3
4
5
6
7
| DBCC SQLPERF(LOGSPACE)
BACKUP LOG Comapny WITH TRUNCATE_ONLY
DBCC SHRINKFILE (Company_log, 500)
DBCC SQLPERF(LOGSPACE) |
其他一些答案对我不起作用：数据库联机时无法创建检查点，因为事务日志已满(具有讽刺意味)。但是，将数据库设置为紧急模式后，我可以缩小日志文件：
1
2
3
4
5
6
| alter database <database_name> set emergency;
use <database_name>;
checkpoint;
checkpoint;
alter database <database_name> set online;
dbcc shrinkfile(<database_name>_log, 200); |
数据库事务日志缩小到最小大小：
备份：事务日志
缩小文件：事务日志
备份：事务日志
缩小文件：事务日志
我对多个数据库进行了测试：此序列有效。
通常缩小到2MB。
或通过脚本：
1
2
3
4
5
6
7
8
9
10
11
12
13
| DECLARE @DB_Name nvarchar(255);
DECLARE @DB_LogFileName nvarchar(255);
SET @DB_Name = '<Database Name>'; --Input Variable
SET @DB_LogFileName = '<LogFileEntryName>'; --Input Variable
EXEC
(
'USE ['+@DB_Name+']; '+
'BACKUP LOG ['+@DB_Name+'] WITH TRUNCATE_ONLY ' +
'DBCC SHRINKFILE( '''+@DB_LogFileName+''', 2) ' +
'BACKUP LOG ['+@DB_Name+'] WITH TRUNCATE_ONLY ' +
'DBCC SHRINKFILE( '''+@DB_LogFileName+''', 2)'
)
GO |
