SQL Server 2008 - 缩小事务日志 - 有什么方法可以自动化? [英] SQL Server 2008 - Shrinking the Transaction Log - Any way to automate?
问题描述
前几天我进去查看了我的交易日志，发现它像 15GB 一样疯狂.我运行了以下代码:
I went in and checked my Transaction log the other day and it was something crazy like 15GB. I ran the following code:
USE mydb
GO
BACKUP LOG mydb WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE(mydb_log,8)
GO
运行良好，将其缩小到 8MB...但有问题的数据库是 Log Shipping Publisher，并且日志已经备份到大约 500MB 并且增长很快.
Which worked fine, shrank it down to 8MB...but the DB in question is a Log Shipping Publisher, and the log is already back up to some 500MB and growing quick.
除了创建自定义的执行 T-SQL 语句任务"维护计划任务并将其挂接到我的日志备份任务之外，是否有任何方法可以自动执行此日志收缩?如果那是最好的方法，那很好……但我只是在想 SQL Server 会有更好的方法来处理这个问题.我认为它应该在您进行日志备份时自动缩小，但这并没有发生(也许是因为我的日志传送，我不知道).
Is there any way to automate this log shrinking, outside of creating a custom "Execute T-SQL Statement Task" Maintenance Plan Task, and hooking it on to my log backup task? If that's the best way then fine...but I was just thinking that SQL Server would have a better way of dealing with this. I thought it was supposed to shrink automatically whenever you took a log backup, but that's not happening (perhaps because of my log shipping, I don't know).
这是我目前的备份计划:
Here's my current backup plan:
- 每晚完整备份
- 每天早上备份一次事务日志(也许可以将日志缩小到这个...不过不需要每天都缩小)
或者也许我只是在运行完整备份任务后每周运行一次?大家怎么看?
Or maybe I just run it once a week, after I run a full backup task? What do you all think?
推荐答案
如果您的文件每晚都以 500 MB 的速度增长，那么只有一个正确的操作:将文件预增长到 500MB 并保持不变.缩小日志文件是有害的.日志文件自动增长也会造成损害.
If you file grows every night at 500 MB there is only one correct action: pre-grow the file to 500MB and leave it there. Shrinking the log file is damaging. Having the log file auto-grow is also damaging.
- 您在正常操作期间达到了文件增长零填充初始化，从而降低了性能
- 您的日志以很小的增量增长，从而创建了许多虚拟日志文件，从而降低了操作性能
- 您的日志在收缩期间会变得碎片化.虽然不像数据文件碎片那么糟糕，但日志文件碎片仍然会影响性能
- 有一天，每天增加 500MB 的磁盘空间会耗尽，而您希望文件是预先增长的
你不必相信我的话，你可以在一些 MVP 博客上阅读他们对定期日志和文件收缩实践的看法:
You don't have to take my word for it, you can read on some of the MVP blogs what they have to say about the practice of log and file shrinkage on a regular basis:
还有更多，我只是厌倦了链接它们.
There are more, I just got tired of linking them.
每缩小一个日志文件，仙女就会失去她的翅膀.
这篇关于SQL Server 2008 - 缩小事务日志 - 有什么方法可以自动化?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
