logo标签列表

大型表的归档（SQL Server 2008）

sqlsql-serversql-server-2008
5

我有一个非常大的表格，每个季度都会填充大约一亿条记录。

我使用this script手动将数据从现有表格移动到另一个数据库，以最小化备份大小，并在执行查询时卸载生产数据库。

是否有更好的方法，例如，一些定期脚本可以有效地将数据从生产数据库移动到其他数据库，然后每天或每周从源数据库中删除记录？

请注意，由于向此表格插入的数量很高，我的日志文件正在迅速增长，而且当我将数据移动到归档数据库时，DELETEs也将被记录。

谢谢

你避免分区的原因是什么？这似乎是一个完美的使用案例。我不是SQLServer分区专家，所以我会让其他人介入具体情况，但是对于这种规模，交换分区似乎是最好的解决方案。 - Data Masseur
4个回答
7

让我总结一下需求：

  1. 减小备份文件大小
  2. 通过归档来减少数据库中的记录数量
  3. 在不产生过多日志的情况下实现数据归档

为了减小备份文件大小，您需要将数据移动到另一个数据库中。

关于日志记录，您需要查看最少日志规则并确保您正在遵循它们。确保您插入的数据库的恢复模型是简单或批量日志记录恢复模型。

对于插入存档数据，您需要禁用非聚集索引（并在插入完成后重建它们），如果有聚集索引，则利用跟踪标志610，并在目标表上放置表锁定。还有许多链接中的规则需要检查，但这些是基础知识。

删除没有最小日志记录，但可以使用top子句分块删除以尽量减少日志文件增长。基本思路是（在删除期间切换到简单恢复模型以限制文件增长）：

SELECT NULL;

WHILE @@ROWCOUNT > 0

     DELETE TOP (50000) FROM TABLE WHERE Condition = TRUE;

调整顶部数字以调整每个删除操作的日志记录量。您还需要确保谓词条件正确，以便只删除您打算删除的内容。这将删除50000个，然后如果返回行数统计信息，则会重复此过程，直到返回的行数为0。
如果您真的希望所有内容都有最少的日志记录，可以按周对源表进行分区，创建源表的克隆副本（在同一分区函数和相同索引结构上），将分区从源表切换到克隆表，从克隆表插入存档表，然后截断克隆表。这样做的好处是使用truncate而不是delete。缺点是设置、维护和查询要复杂得多（每个分区都有一个堆或B树，因此如果所有查询不利用分区消除，则聚集索引/表扫描必须扫描多个B树/堆，而不仅仅是一个）。
3

您有没有考虑使用SSIS来完成此任务。我使用SSIS按顺序进行归档和备份。您也可以在tsql任务中使用相同的脚本，并使用代理程序进行调度。或者您可以仅使用代理程序，并将脚本粘贴到其中。

2
2
分区，绝对是必须的。这将消除需要新数据库的需求。好的例子在这里。
如果您不想更改架构，我建议使用SSIS移动数据而不是脚本。
我喜欢使用分区进行归档，并认为它是解决方案的一部分，但这并不会减少备份大小，这是OP的要求之一。 - brian
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接