我不是 SQL 专家,每次我需要做一些超出基础的事情时,我都会想起这样一个事实。我有一个规模不大的测试数据库,但事务日志肯定是。如何清除事务日志?
确实应该为遇到意外增长而您不希望再次发生的情况保留使日志文件更小的情况。如果日志文件将再次增长到相同的大小,则暂时缩小它并不能完成很多工作。现在,根据您的数据库的恢复目标,这些是您应该采取的行动。
首先,进行完整备份
永远不要对数据库进行任何更改,除非确保在出现问题时可以恢复它。
如果您关心时间点恢复
(通过时间点恢复,我的意思是您关心能够恢复到除完整备份或差异备份之外的任何内容。)
大概您的数据库处于 FULL
恢复模式。如果不是,请确保它是:
ALTER DATABASE testdb SET RECOVERY FULL;
即使您定期进行完整备份,日志文件也会不断增长,直到您执行日志备份 - 这是为了保护您,而不是不必要地占用您的磁盘空间。根据您的恢复目标,您应该非常频繁地执行这些日志备份。例如,如果您的业务规则规定您可以承受在发生灾难时丢失不超过 15 分钟的数据,那么您应该有一个每 15 分钟备份一次日志的作业。这是一个脚本,它将根据当前时间生成带时间戳的文件名(但您也可以使用维护计划等来执行此操作,只是不要在维护计划中选择任何收缩选项,它们很糟糕)。
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,那么您可以通过以下方式调整日志文件大小:
USE [master];
GO
ALTER DATABASE Test1
MODIFY FILE
(NAME = yourdb_log, SIZE = 200MB, FILEGROWTH = 50MB);
GO
请注意,如果日志文件当前 > 200 MB,您可能需要先运行:
USE yourdb;
GO
DBCC SHRINKFILE(yourdb_log, 200);
GO
如果您不关心时间点恢复
如果这是一个测试数据库,并且您不关心时间点恢复,那么您应该确保您的数据库处于 SIMPLE
恢复模式。
ALTER DATABASE testdb SET RECOVERY SIMPLE;
将数据库置于 SIMPLE
恢复模式将确保 SQL Server 重复使用部分日志文件(实质上是逐步淘汰非活动事务)而不是增长以保留 所有 事务的记录(如 { 2} 恢复直到您备份日志)。 CHECKPOINT
事件将有助于控制日志并确保它不需要增长,除非您在 CHECKPOINT
之间生成大量 t-log 活动。
接下来,您应该绝对确保此日志增长确实是由于异常事件(例如,年度春季大扫除或重建最大索引),而不是由于正常的日常使用。如果您将日志文件缩小到小得离谱的大小,而 SQL Server 只需要再次增大它以适应您的正常活动,那么您得到了什么?您是否能够利用您只是暂时释放的磁盘空间?如果您需要立即修复,则可以运行以下命令:
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 中不再可用。其次,如果您处于完整恢复模式,这将破坏您的日志链并需要新的完整备份。
分离数据库,删除日志文件,然后重新附加。我无法强调这有多危险。您的数据库可能无法备份,可能会出现可疑情况,您可能必须恢复到备份(如果有的话)等等。
使用“收缩数据库”选项。 DBCC SHRINKDATABASE 和维护计划选项做同样的事情是坏主意,特别是如果你真的只需要解决一个日志问题问题。使用 DBCC SHRINKFILE 或 ALTER DATABASE ... MODIFY FILE(上面的示例)定位您要调整的文件并独立调整它。
将日志文件缩小到 1 MB。这看起来很诱人,因为,嘿,SQL Server 会让我在某些情况下这样做,并查看它释放的所有空间!除非您的数据库是只读的(并且您应该使用 ALTER DATABASE 将其标记为只读),否则这绝对会导致许多不必要的增长事件,因为无论恢复模型如何,日志都必须容纳当前事务。暂时释放该空间有什么意义,以便 SQL Server 可以缓慢而痛苦地收回它?
创建第二个日志文件。这将为已填满磁盘的驱动器提供暂时的缓解,但这就像试图用创可贴修复被刺破的肺一样。您应该直接处理有问题的日志文件,而不是仅仅添加另一个潜在问题。除了将某些事务日志活动重定向到不同的驱动器之外,第二个日志文件实际上对您没有任何作用(与第二个数据文件不同),因为一次只能使用其中一个文件。 Paul Randal 还解释了为什么多个日志文件会在以后给您带来麻烦。
主动
与其将日志文件缩小到很小的数量并让它自己以很小的速率不断地自动增长,不如将它设置为相当大的大小(一个可以容纳最大并发事务集的总和的大小)并设置一个合理的自动增长设置为后备,因此它不必多次增长以满足单个事务,因此在正常业务操作期间它相对很少需要增长。
这里最糟糕的设置是 1 MB 增长或 10% 增长。有趣的是,这些是 SQL Server 的默认值(我曾抱怨过 asked for changes to no avail)——1 MB 用于数据文件,10% 用于日志文件。前者在当今时代太小了,后者每次都会导致越来越长的事件(例如,您的日志文件是 500 MB,第一次增长是 50 MB,下一个增长是 55 MB,下一个增长是 60.5 MB ,等等等等 - 在慢速 I/O 上,相信我,你会真正注意到这条曲线)。
进一步阅读
请不要停在这里;虽然您看到的关于缩减日志文件的许多建议本质上都是不好的,甚至可能是灾难性的,但有些人更关心数据完整性而不是释放磁盘空间。
A blog post I wrote in 2009, when I saw a few "here's how to shrink the log file" posts spring up。
A blog post by Paul Randal explaining why t-log maintenance is important 和 why you shouldn't shrink your data files, either。
-- 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 年前所说:
如果有人对这不是适当或最佳解决方案的情况有任何意见要添加,请在下面发表评论
右键单击数据库名称。
选择任务→收缩→数据库
然后点击确定!
我通常打开包含数据库文件的 Windows 资源管理器目录,因此我可以立即看到效果。
我真的很惊讶这能奏效!通常我以前使用过 DBCC,但我只是尝试过它并没有缩小任何东西,所以我尝试了 GUI(2005 年)并且效果很好 - 在 10 秒内释放了 17 GB
在完全恢复模式下,这可能不起作用,因此您必须先备份日志,或者更改为简单恢复,然后收缩文件。 [为此感谢@onupdatecascade]
--
PS:我很欣赏一些人对此危险的评论,但在我的环境中,我自己没有任何问题,特别是因为我总是先做完整备份。因此,在继续之前,请考虑您的环境是什么,以及这如何影响您的备份策略和工作安全性。我所做的只是向人们指出微软提供的一项功能!
下面是一个收缩事务日志的脚本,但我绝对建议在收缩之前备份事务日志。
如果您只是缩小文件,您将丢失大量数据,这些数据可能会在发生灾难时成为救生员。事务日志包含许多有用的数据,可以使用第三方事务日志阅读器读取(可以手动读取,但需要付出极大的努力)。
在进行时间点恢复时,事务日志也是必须的,所以不要只是扔掉它,而是要确保事先备份它。
以下是人们使用存储在事务日志中的数据来完成恢复的几篇文章:
如何在 SQL Server 2008 中查看事务日志
在 SQL Server 2008 中读取日志文件 (*.LDF)
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
执行上述命令时,您可能会收到如下所示的错误
“无法缩小日志文件(日志文件名),因为位于文件末尾的逻辑日志文件正在使用中”
这意味着 TLOG 正在使用中。在这种情况下,请尝试连续多次执行此操作,或找到减少数据库活动的方法。
这是一种简单且非常不雅且有潜在危险的方式。
备份数据库 分离数据库重命名日志文件 附加数据库 将重新创建新日志文件 删除重命名日志文件。
我猜你没有做日志备份。 (这会截断日志)。我的建议是将恢复模式从 full 更改为 simple。这将防止日志膨胀。
如果您不使用事务日志进行恢复(即您只进行完整备份),您可以将恢复模式设置为“简单”,事务日志将很快缩小并且不再填满。
如果您使用的是 SQL 7 或 2000,您可以在数据库选项选项卡中启用“truncate log on checkpoint”。这具有相同的效果。
显然不建议在生产环境中这样做,因为您将无法恢复到某个时间点。
Simple
...永远不会再填满”——不正确。我已经看到它发生在恢复模型设置为“简单”的数据库上(过去 48 小时内)。日志文件的文件增长设置为“受限”,我们一直在对其进行一些巨大的活动......我知道这是一种不寻常的情况。 (在我们的情况下,我们有足够的磁盘空间,我们增加了日志文件的大小,并将日志文件文件增长设置为“无限制”......顺便说一下--interface bug--在更改后显示为“受限" 最大大小为 2,097,152 MB。)
不推荐使用 John 推荐的这种技术,因为无法保证数据库在没有日志文件的情况下会附加。将数据库从完整更改为简单,强制检查点并等待几分钟。 SQL Server 将清除日志,然后您可以使用 DBCC SHRINKFILE 将其缩小。
到目前为止,这里的大多数答案都假设您实际上不需要事务日志文件,但是如果您的数据库使用 FULL
恢复模式,并且您希望保留备份以防需要恢复数据库,那么 不要像许多这些答案所建议的那样截断或删除日志文件。
删除日志文件(通过截断、丢弃、擦除等)会破坏您的备份链,并且会阻止您恢复到自上次完整、差异或事务日志备份以来的任何时间点,直到下一次完整备份或进行差异备份。
我们建议您永远不要使用 NO_LOG 或 TRUNCATE_ONLY 手动截断事务日志,因为这会破坏日志链。在下一次完整或差异数据库备份之前,数据库不会受到介质故障的保护。仅在非常特殊的情况下使用手动日志截断,并立即创建数据备份。
为避免这种情况,请在缩小日志文件之前将其备份到磁盘。语法看起来像这样:
BACKUP LOG MyDatabaseName
TO DISK='C:\DatabaseBackups\MyDatabaseName_backup_2013_01_31_095212_8797154.trn'
DBCC SHRINKFILE (N'MyDatabaseName_Log', 200)
, 1)
部分。问题是,如果将其缩小到 1 MB,导致 正常 日志大小的增长事件将非常昂贵,如果增长将有 许多率保留为默认值 10%。
SQL Server 事务日志需要妥善维护,以防止其不必要的增长。这意味着足够频繁地运行事务日志备份。如果不这样做,您将面临事务日志变满并开始增长的风险。
除了这个问题的答案,我建议阅读和理解事务日志常见的神话。这些读数可能有助于理解事务日志并决定使用什么技术来“清除”它:
来自 10 most important SQL Server transaction log myths:
误解:我的 SQL Server 太忙了。我不想进行 SQL Server 事务日志备份 SQL Server 中最大的性能密集型操作之一是联机事务日志文件的自动增长事件。由于不经常进行事务日志备份,在线事务日志将变满并且必须增长。默认增长大小为 10%。数据库越忙,如果未创建事务日志备份,则联机事务日志增长得越快 创建 SQL Server 事务日志备份不会阻止联机事务日志,但自动增长事件会。它可以阻止在线事务日志中的所有活动
误区:定期收缩日志是一种很好的维护实践 FALSE。日志增长非常昂贵,因为新块必须清零。该数据库上的所有写入活动都会停止,直到归零完成,如果您的磁盘写入速度很慢或自动增长大小很大,那么该暂停可能会很大并且用户会注意到。这就是你想要避免增长的原因之一。如果你收缩日志,它会再次增长,你只是在不必要的收缩和增长游戏上浪费磁盘操作
使用 DBCC ShrinkFile ({logicalLogName}, TRUNCATEONLY)
命令。如果这是一个测试数据库并且您正在尝试保存/回收空间,这将有所帮助。
请记住,虽然 TX 日志确实有一种最小/稳定状态大小,但它们会长大。根据您的恢复模式,您可能无法缩小日志 - 如果在 FULL 并且您没有发出 TX 日志备份,则无法缩小日志 - 它将永远增长。如果您不需要 TX 日志备份,请将您的恢复模式切换为简单。
请记住,在任何情况下都不要删除日志 (LDF) 文件!您几乎会立即发生数据库损坏。煮熟!完毕!丢失数据!如果“未修复”,主 MDF 文件可能会永久损坏。
永远不要删除事务日志——你会丢失数据!您的部分数据在 TX 日志中(无论恢复模式如何)...如果您分离并“重命名”有效删除部分数据库的 TX 日志文件。
对于那些已删除 TX 日志的用户,您可能需要运行一些 checkdb 命令并在丢失更多数据之前修复损坏。
查看 Paul Randal 关于这个主题的博文,bad advice。
此外,通常不要在 MDF 文件上使用 shrinkfile,因为它会严重分割您的数据。查看他的“坏建议”部分了解更多信息(“为什么不应该缩小数据文件”)
查看 Paul 的网站 - 他涵盖了这些问题。上个月,他在他的 Myth A Day 系列中讨论了许多这些问题。
要截断日志文件:
备份数据库
通过使用企业管理器或通过执行分离数据库:Sp_DetachDB [DBName]
删除事务日志文件。 (或重命名文件,以防万一)
使用:Sp_AttachDB [DBName] 再次重新附加数据库
附加数据库后,将创建一个新的事务日志文件。
缩小日志文件:
带有 No_Log 的备份日志 [DBName]
通过以下任一方式收缩数据库:使用企业管理器:- 右键单击数据库,所有任务,收缩数据库,文件,选择日志文件,确定。使用 T-SQL :- Dbcc Shrinkfile ([Log_Logical_Name])
您可以通过运行 sp_helpdb 或通过在企业管理器中查看数据库的属性来找到日志文件的逻辑名称。
首先检查数据库恢复模式。默认情况下,SQL Server Express Edition 为简单恢复模型创建一个数据库(如果我没记错的话)。
带有 Truncate_Only 的备份日志 DatabaseName:
DBCC ShrinkFile(yourLogical_LogFileName, 50)
SP_helpfile 将为您提供逻辑日志文件名。
参考:
Recover from a full transaction log in a SQL Server database
如果您的数据库处于完全恢复模式并且不进行 TL 备份,则将其更改为 SIMPLE。
TRUNCATE_ONLY
在当前版本的 SQL Server 中不再是一个选项,并且不建议在支持它的版本上使用它(请参阅 Rachel's answer)。
它发生在我的数据库日志文件为 28 GB 的地方。
你能做些什么来减少这种情况?实际上,日志文件是 SQL 服务器在事务发生时保留的那些文件数据。对于要处理的事务,SQL 服务器会为其分配页面。但交易完成后,这些都不是突然放出来的,希望有可能有同样的交易来。这占据了空间。
第1步:首先在数据库查询explored checkpoint中运行这个命令
步骤2:右键单击数据库任务>备份选择备份类型为事务日志添加目标地址和文件名以保留备份数据(.bak)
再次重复此步骤,此时给出另一个文件名
https://i.stack.imgur.com/ELNLW.png
第 3 步:现在转到数据库 右键单击数据库
任务>收缩>文件选择文件类型作为日志收缩操作作为释放未使用的空间
https://i.stack.imgur.com/qwPKt.png
第4步:
在 SQL 2014 中正常检查您的日志文件,这可以在以下位置找到
C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014EXPRESS\MSSQL\DATA
就我而言,它从 28 GB 减少到 1 MB
根据我在大多数 SQL Server 上的经验,没有事务日志的备份。完全备份或差异备份是常见的做法,但事务日志备份确实很少见。因此事务日志文件永远增长(直到磁盘已满)。在这种情况下,恢复模式应设置为“简单”。不要忘记修改系统数据库“model”和“tempdb”。
数据库“tempdb”的备份没有意义,因此该数据库的恢复模式应该始终是“简单”的。
备份 MDB 文件。停止 SQL 服务 重命名日志文件 启动服务
(系统将创建一个新的日志文件。)
删除或移动重命名的日志文件。
数据库→右键单击属性→文件→添加另一个具有不同名称的日志文件,并将路径设置为与具有不同文件名的旧日志文件相同。
数据库自动选取新创建的日志文件。
尝试这个:
USE DatabaseName
GO
DBCC SHRINKFILE( TransactionLogName, 1)
BACKUP LOG DatabaseName WITH TRUNCATE_ONLY
DBCC SHRINKFILE( TransactionLogName, 1)
GO
TRUNCATE_ONLY
在当前版本的 SQL Server 中不再是一个选项,并且不建议在支持它的版本上使用它(请参阅 Rachel's answer)。
略微更新的答案,适用于 MSSQL 2017,并使用 SQL 服务器管理工作室。我主要是按照这些说明https://www.sqlshack.com/sql-server-transaction-log-backup-truncate-and-shrink-operations/
我最近有一个数据库备份,所以我备份了事务日志。然后我再次备份它以备不时之需。最后我缩小了日志文件,从 20G 到 7MB,更符合我的数据大小。我认为自从 2 年前安装以来,事务日志就没有被备份过。所以把这个任务放在了家务日历上。
备份数据库分离数据库重命名日志文件附加数据库(在附加删除重命名的.ldf(日志文件)时。选择它并按删除按钮删除)将重新创建新的日志文件删除重命名的日志文件。
这将起作用,但建议先备份您的数据库。
其他一些答案对我不起作用:数据库在线时无法创建检查点,因为事务日志已满(多么讽刺)。但是,将数据库设置为紧急模式后,我能够缩小日志文件:
alter database <database_name> set emergency;
use <database_name>;
checkpoint;
checkpoint;
alter database <database_name> set online;
dbcc shrinkfile(<database_name>_log, 200);
数据库事务日志缩小到最小大小:
备份:事务日志 收缩文件:事务日志 备份:事务日志 收缩文件:事务日志
我对几个数据库进行了测试:这个序列有效。
它通常缩小到 2MB。
或通过脚本:
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
TRUNCATE_ONLY
在当前版本的 SQL Server 中不再是一个选项,并且不建议在支持它的版本上使用它(请参阅 Rachel's answer)。
不定期副业成功案例分享