SQL Server 清理数据库、事务日志？
我有一个 SQL Server 2005 数据库，我要从中删除几个大表到存档数据库。原始数据库应该大幅缩小。
为了创建存档数据库，我将恢复原始数据库的副本，并从中删除当前的表。
这是最好的方法吗？我应该如何处理原木/收缩以确保最终尺寸尽可能小？存档数据库可能会增长一点，但原始数据库会继续正常增长。
I have a SQL Server 2005 database from which I'm removing several large tables to an archive database. The original database should shrink considerably.
To make the archive database, I was going to restore a copy of the original and just remove the current tables from that.
Is this the best way to go about it? What should I do with logs/shrinking to make sure the final sizes are as small as possible? The archive database may grow a little, but the original continues its normal growth.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这似乎是一个不错的方法。将恢复模式设置为简单，然后截断并收缩日志文件。这将使其尽可能小。
请参阅此处了解执行此操作的好方法。
注意：这假设您不希望或不需要将存档数据库恢复到特定时间点。原因是简单恢复模型不会将事务保存在事务日志中。因此，当您的存档数据库发生“一点点”变化（正如您所说）时，它不会将事务保存到日志中。
That seems like an ok way to do it. Set the recovery model to simple, then truncate and shrink the log files. This will make it as small as possible.
See here for a good way to do it.
Note: This assumes you don't want or need to recover the archive database back to specific points in time. The reason being that Simple recovery model does not save the transactions in a transaction log. So as your archive database changes "a little" (as you said), it won't save the transactions to a log.
我使用这个脚本，这在开发中非常有用。
I use this script and this is very useful in developing.
尝试以下 SQL 命令之一：
或者右键单击 Sql Server Management Studio 对象资源管理器的数据库并选择任务 -收缩。
Try one of these sql commands:
Or right click into Sql Server Management Studio Object Explorer's database and select Tasks-Shrink.