如何管理大型大容量数据库中的日志文件大小

Question

我们是一个数学家团队(即没有DBA经验)。

我们在Server 2012中有一个大型数据库。它有超过2TB的数据(数百个表，每个表都有数百万行和数百列宽)。每个月，我们都会收到对数据的大量添加和修订，要求我们通过删除、替换或更新大部分或所有表来对数据库执行广泛的更新。

我们的工作主要集中在构建SQL逻辑来计算我们需要的结果。我们不是在经营实时呼叫中心。我们根据需要应用了几个索引，我们对性能非常满意。

问题是日志文件。自然，日志文件随着大量的数据操作而增长。我们的日志文件目前约为1TB。我们有大量的磁盘空间，但它并不是无限的。

根据我们在Internet上所读到的内容，我们了解到日志文件对于事务完整性、回滚和恢复是必要的。但为了我们的具体目的，我们不关心这些。我们可能永远不会执行回滚，也不会尝试恢复。更糟的是，我们只需再次下载数据文件并从头创建一个新的数据库。

我们只想让日志文件离开，再也不回来了。

我们将数据库恢复模式设置为简单、天真的想法，认为这意味着“没有恢复模式”，但我们很快就消除了这些幻想。

我们也明白，有许多错误的事情不能做(分离，收缩，等等)。我们只是不知道该做什么。

也许有人会建议我们设定一个日志文件增长的限制。然而，这就留下了两个问题：(1)如何摆脱已经存在的1TB？(2)我们以前尝试过这样做，当我们接近指定的限制时，我们开始在这里、那里和任何地方得到错误9002 (日志文件已满)。因此，现在我们害怕应用一个大小限制。

我们如何告诉数据库“没有日志文件，请”没有任何伤害的感情？

Answer 1

如果您从未预期过执行实时恢复，那么您只需要做两件事：

1. 将数据库更改为简单恢复模式
2. 在小批量中做大的改变。

在简单恢复模式下，日志文件仅用于进程中的事务。事务完成后，tran日志中的空间将被标记为可重用，下一个事务将只是重复使用空间*。

(*严格地说，有些“检查点”偶尔运行，实际上为重复使用腾出了空间，但在很大程度上，它在不需要干预的情况下起作用；只有挑剔的DBA才会关心这里的内部细节。)

所以保持你的交易规模：

• 如果您需要删除一个表的所有行，不要执行一个100 m行的DELETE (这会炸掉日志)，而是执行一个TRUNCATE，或者只执行DROP并重新创建它。
• 如果您不能这样做(您只需要删除一些行)，小批量地做
• 在进行大型数据导入时，了解批量导入或快速加载数据并经常提交数据的SSIS快速加载。
• 对于更新，不要更新大量表的每一行，使用更具选择性的WHERE子句，每次将更新限制在大约100 k行。了解如何在SQL中使用游标.

当然，确切的细节将取决于您的实际数据库和实际活动，但是有1TB数据的数据库不应该有一个比100 1TB更大的tran日志文件，这取决于您的批处理的大小。

关于缩小现有文件，您可以使用以下脚本查看数据和日志文件中的空闲/使用空间：

SELECT DB_NAME() as dbname, type_desc, name as logical_name, 
    CONVERT(decimal(12,1),size/128.0) as TotalMB,
    CONVERT(decimal(12,1),FILEPROPERTY(name,'SpaceUsed')/128.0) as UsedMB,
    CONVERT(decimal(12,1),(size - FILEPROPERTY(name,'SpaceUsed'))/128.0) as FreeMB,
    physical_name
 FROM sys.database_files WITH (NOLOCK)
 ORDER BY type, file_id;

如果日志中使用的空间很高，那么您可能仍然有打开的事务。寻找第三方工具sp_WhoIsActive，以深入了解您当前的活动/开放转换。

当您准备收缩时，选择一个合理的目标大小，并执行SHRINKFILE：

DBCC SHRINKFILE (NAME = 'mylogfile', SIZE = 100000)

(该大小为MB)。

如果您的日志文件还在增长，请返回sp_WhoIsActive查看哪些事务正在运行这么长时间，并研究如何分批处理这些事务。

Answer 2

我只想再修改一件事。

提醒原始海报，“不(事务)日志文件，请”是不可能实现的，这一点可能很重要-与一些可以完全禁用的简单网络服务的其他日志机制不同(例如，web服务器)，即使在简单的恢复模型中，事务日志也永远无法被处理。不鼓励对事务日志文件施加大小限制，因为当您意外执行一些大批时，它将导致您的错误9002。相反，最好关注在Server数据库上执行的批处理大小，从而将事务日志文件包含到某种可接受的大小。

Answer 3

这里是处理非DBA或的1TB日志。假设您安装了SSMS。

右键单击数据库有巨大的日志文件。

请选择“->文件类型:日志”，您将看到“可用的空闲空间：”。在“收缩”操作下，在释放未使用的空间之前重新组织页，将文件收缩到？MB(最小值为0 MB)。指定值之后，然后确定。