是否可以使用 DBCC SHRINKFILE 收缩链接服务器上的日志文件？
我正在将大量数据从一个数据库归档到链接服务器上的另一个数据库。我正在从 MS sql server 2000 复制到 MS sql server 2005。 这涉及复制数百万条记录然后删除它们。这一切都是通过动态脚本发生的。我想在此过程中合并日志文件的收缩，但我无法使 DBCC SHRINKFILE 适用于链接服务器。这可能吗？
I am doing a large archival of data from one DB to another DB on a linked server. I am copying from MS sql server 2000 to MS sql server 2005.
This involves copying millions of records and then deleting them. This all happens via a dynamic script. I would like to incorporate the shrinking of the log file for this process, but I am unable to get the DBCC SHRINKFILE to work for the linked server. Is this even possible?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您应该能够通过链接服务器调用
sp_executesql来完成此操作：不过，您可能应该重新考虑自动收缩日志文件，特别是如果该脚本运行得如此频繁以至于您需要将其自动化。您最好备份日志或将恢复模式切换为简单，以便可以重用空间。 （如果它要恢复到相同的大小，您真的通过缩小它来完成任何事情吗？事实上，您可能会因无意中引入文件系统碎片而使事情变得更糟。）
我并不是说您永远不需要以编程方式缩小文件，但想要这样做通常表明存在其他问题 - 磁盘对于您的预期卷来说太小，您的恢复模式错误等。
You should be able to do it by calling
sp_executesqlthrough the linked server:You should probably rethink automatically shrinking the log file, though, especially if this script will be run so often that you need to automate it. You'll be better off backing up the log or switching the recovery model to Simple so you can reuse the space. (If it's going to grow back to the same size, did you really accomplish anything by shrinking it? In fact, you can make things worse by inadvertently introducing filesystem fragmentation.)
I'm not saying you never need to shrink a file programmatically, but wanting to do so is usually a sign something else is wrong -- the disk is too small for your expected volume, you have the wrong recovery model, etc.