如何在不增加日志文件的情况下重新加载表？
我有以下场景，每天晚上使用 SQL Server 2008 中的 SSIS 运行：
- 创建一些临时文件。 从 ODBC 数据源导入数据
- 表，并在导入完成后 ，删除现有表，并将 temp.table 重命名为 temp.table。表与删除的表同名
我需要在 SSIS 作业或 SQL Server 设置中执行哪些操作才能最大限度地减少日志记录？我不想关闭整个数据库的日志记录，因为只有这几个表每晚更新。
I have the following scenario which runs every night using SSIS in SQL Server 2008:
- create a few temp. tables, and import data from an ODBC data source
- when the imports are complete, drop existing tables, and rename the temp. tables to the same name as the dropped tables
What do I need to do in either my SSIS job or SQL Server settings to minimize the logging? I don't want to turn off logging for the whole db, since there are only these few tables that get updated nightly.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
我会将这些表放在另一个启用了简单恢复的数据库中，然后在另一个数据库中使用同义词使它们显示在本地。
这将使您能够利用其他人为这些表推荐的简单恢复模型和快速加载选项。
I would put these tables in another db with simple recovery enabled, and then use a synonym in the other database to make them appear local.
This will let you leverage the simple recovery model and fast load option recommended by others for these tables.
我认为最好的选择是在表加载之前立即将恢复模式更改为批量记录，然后切换回完整模式。请务必阅读要考虑的事项 (http://msdn.microsoft. com/en-us/library/ms190203.aspx）在实施之前。这可以通过 ALTER DATABASE 语句来完成，这些语句可以添加到 SSIS 包的控制流中：
虽然我（目前）也依赖更频繁的日志备份来保持日志驯服，但这更像是艺术而不是科学。就像大多数艺术一样，总有一些东西会让你感到惊讶；不幸的是，这些惊喜更像是去看牙医：痛苦但可能是可以预防的:)。
我还想知道您是否在 oledb 目标组件上使用快速加载选项（假设您正在使用该选项）。这也应该是最少的日志记录。有一个很好的白皮书，介绍了多种技术加载可能值得检查的数据时的性能（做好准备，这是一件好事）。 。 。也许它可以给你更多的想法！
I would think that changing the recovery model to be bulk-logged immediately prior to the table loads then switching back to full would be the best option. Be sure to read up on things to consider (http://msdn.microsoft.com/en-us/library/ms190203.aspx) before implementation. This can be accomplished via ALTER DATABASE statements that could be added to the control flow of your SSIS package:
Though I also have (and currently) relied on more frequent log backups to keep the log tamed, this is more art than science. Like most art, something's bound to surprise you; unfortunately, these surprises are more like a visit to the dentist: painful and probably preventable :).
I would also wonder if you're using the fast-load option on the oledb destination component (presuming that's what you're using). This should be minimally logging as well. There's a good white paper on numerous techniques for performance when loading data that might be worth checking out as well (be prepared, it's a doozey) . . . perhaps it can give you a few more ideas!
更频繁地备份事务日志可以保持其大小，并且不需要增加日志大小。我还发现现在存储空间很便宜，您可以将 LDF 文件重新定位到该驱动器以方便释放可用空间。
More frequent backups to transaction log can keep up the size and not demanding for increase in log size. Also I see that storage is cheap now a days where you can relocate your LDF file to that drive for ease of free space.
是否有可能将这些表拆分到另一个数据库中？
您不仅可以单独管理日志记录设置，还可以管理日志和备份本身，因此您可以更精细地控制它。
您可以在旧数据库中创建视图，该视图指向您移动到新数据库的表，因此代码甚至可能不需要进行太多更改。
Is there a possibility of splitting these tables out into another database?
You would be able to manage not only the logging settings separately, but the logs and backups themselves and thus you could control it with much more granularity.
You could create views in the old database which point to the tables you move to the new database, so code might not even need to change much.
不幸的是，日志记录是数据库级别的设置。也许您可以将负载加载到单独的数据库中（使用 SIMPLE 或 BULK LOGGED），然后假设该位成功，则更新现有表。
Unfortunately logging is a database level setting. Perhaps you could do the load into a separate DB (with SIMPLE or BULK LOGGED) and then, presuming that bit is successful, UPDATE your existing tables.