SQL 2012 事务日志备份不会截断日志

SQL 2012 事务日志备份不会截断日志

我是数据库管理的新手,所以请多多包涵。我有一个 500MB 的小型数据库,但事务日志非常大(19GB)。我想将其保持在“完整恢复”模式,所以请不要建议使用“简单”恢复模式。

我一直在研究如何减少事务日志的大小,并尝试实施建议,但日志大小并没有改变。

首先,我的做法如下:我有一个每日备份任务,用于备份维护计划内的所有数据库。这是一种“完整”备份类型,似乎效果不错。我看到每天备份一个文件,大小与数据库本身的大小相似。

现在我有了完整的备份,我继续进行手动“事务日志”类型的备份,并选择“截断事务日志”。

备份在几秒钟内完成,创建一个几兆字节大小的文件,但事务日志的大小保持不变。

我究竟做错了什么?

答案1

您的事务日志备份正在截断日志,因为它在现有日志文件中为更多事务腾出空间。如果您想缩小日志文件,您需要在 SSMS 中选择“缩小文件”选项。右键单击数据库以找到该选项。
MS SQL Server Management Studio 收缩文件 MS SQL Server Management Studio 收缩日志文件 如果缩小的文件大小不够大,则根据数据库的事务数量以及备份日志的频率,文件大小会再次增大。您可能需要更频繁地运行日志备份以防止这种情况发生。

听起来您根本没有运行事务日志备份(除了手动运行的那一次)。如果是这样,这不仅是您的事务日志增长的原因,而且无论如何您也无法获得完全恢复的好处。请安排定期的事务日志备份。(每小时备份一次是个不错的开始。)

答案2

好的,首先要理解的是日志文件为什么这么大。您说数据是 500MB,您确定吗?与日志文件相比,这是一个非常小的数据文件。如果这是真的,那么日志的大小是有原因的。

所以你必须弄清楚为什么……你一直在将数据运行到数据库中吗?是否有任何 ETL/DTS 进程正在运行数据、重建索引等,以及正在运行大型事务?你检查过没有未提交的事务吗?(选择 @@trancount)

您可以通过检查log_reuse_wait和log_reuse_wait_desc来检查日志的当前状态。

从 [master].[sys].[databases] 中选择 [log_reuse_wait_desc],其中 [name] = N'Company';

日志的非活动部分不能被截断,直到其所有日志记录都被捕获到日志备份中。这是维护日志链所必需的,日志链是一系列具有连续日志序列号 (LSN) 序列的日志记录。假设存在以下条件,则在备份事务日志时会截断日志:

1.自上次备份日志以来,已发生检查点。检查点是必不可少的,但不足以在完整恢复模式或大容量日志恢复模式下截断日志。检查点之后,日志至少在下一次事务日志备份之前保持完整。

2.没有其他因素阻碍日志事务。

通常,通过定期备份,日志空间会定期释放以供将来使用。但是,各种因素(例如长时间运行的事务)可能会暂时阻止日志截断。

BACKUP LOG 语句未指定 WITH COPY_ONLY。

来源:https://technet.microsoft.com/en-us/library/ms189085(v=sql.105).aspx

如果日志文件确实需要那么大,而您又缩小了它,那么它就会再次增大,而这本身就是一个代价高昂的过程,因为数据库正在写入日志并需要文件增大,除非您为 SQL 服务器服务帐户打开了即时文件初始化,否则该过程将等待文件增大然后再写入,如果对 GB 块执行此操作,那么您将面临更大的问题需要处理。

所以现在您需要查看数据库的活动,例如,如果您每天仅运行一次日志备份,那么这可能不足以满足数据库的活动需求,在我的组织中,我每 15 分钟到每 1 小时进行一次备份,在某些情况下,每 5 分钟对特定的数据库进行一次备份。这使得日志不会增长太多(某些服务器中的日志已预先调整大小),我们还运行严格的 RTO/RPO。

现在,正如 joeqwerty 所说的那样,除非您对该盒子在恢复点方面有严格的 SLA,否则它可能不值得以 FULL 或 BULK-Logged 运行,您就是在浪费时间,如果您所做的只是每天运行完整备份并且公司不关心数据丢失,请不要反对,而是接受它。

如果您关心的只是日志的大小,而不是转录点恢复,请备份日志并缩小它,然后切换到简单。

您说不要建议简单恢复,但我认为您并不完全了解完整恢复所做的事情。

相关内容