不太了解SQL Server事务日志
我已经阅读了 SQL Server 事务日志，但仍然不太熟悉如何使用/管理它们。我知道它们对于事务回滚、镜像、复制和日志传送等非常重要。
对我来说，它们仍然像一个黑匣子，我不太愿意用它们做任何事情。是否有工具允许我查看事务日志文件或获取有关它的信息？如果我不需要事务日志传送之类的东西，我可以定期缩小和/或截断我的日志文件吗？特别是在将备份恢复到测试实例的情况下 - 我真的需要一个占用空间的多 GB 日志文件吗？我还需要注意哪些其他功能特别依赖于事务日志，并且如果我缩小/截断日志文件，这些功能将无法工作？
I've read up on SQL Server transaction logs, but still not entirely comfortable with how to use/manage them. I get that they are important for things like transaction rollbacks, mirroring, replication and log shipping etc.
To me, they still seem like a black box and I'm not entirely comfortable doing anything with them. Are there tools that allow me to view a transaction log file or get information about it? If I don't need things like transaction log shipping, is it ok for me to shrink and/or truncate my log files periodically? Particularly in the case of restoring backups onto a test instance - do I really need a multi-gigabyte log file taking up space? What other features do I need to be aware of that have particular dependency on transaction logs and would not work if I shrank/truncated log files?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
作为一般规则，您不需要对事务日志“执行”任何操作，除非确保它们不会变得太大。虽然可以使用日志挖掘工具读取它们，但这种情况很少见，并且在您对 DBA 角色的其他方面感到满意之前，不要关心它。
它们是一个黑匣子，并且像这样工作得很好。
您应该选择适合您的业务需求的恢复模型。一般来说，这将涉及确定是否需要时间点恢复，如果需要，可能是“完全恢复模型”，否则是“简单恢复模型”。
如果您确实使用完整恢复模型，那么您需要偶尔备份数据库（每日或每周，差异是这里的常见选项），并且每 15 分钟、一小时或几个小时备份一次事务日志，具体取决于事务量、可用空间和您的数据库。在最坏的情况下您可以承受丢失的最大数据量。
如果您偶尔使用简单恢复备份数据库，但了解您无法进行时间点恢复。
在生产中采用“完全恢复模型”，在开发和测试中采用“简单恢复模型”是很常见的。但您必须确定自己的特殊需求并自己做出决定。
“缩小”事务日志的最安全方法是管理您的备份，这样您就不必明确这样做。
阅读恢复模型并随时提出后续问题。
As a general rule you should not need to 'do' anything with transaction logs except ensure they don't get too big. While it is possible to read from them with log mining tools this is rare and and until you are comfortable with other aspects of the DBA role, don't concern yourself with it.
They are a black box and they work fine like that.
You should select a recovery model that suits your business needs. Generally this will involve determining if you need point in time recovery, if so probably "Full Recovery Model" else "simple Recovery Model".
If you do use Full Recovery Model than you need to backup your DB on occasion (daily or weekly and differentials are common options here) and your transaction logs each 15 minutes, hour couple of hours depending on the volumne of transactions, space available and your maximum amount of data you can afford to lose in the worst case scenario.
If you use Simple recovery backup your DB on occasion but understand that you cannot do point in time recovery.
It's common to have "Full Recovery Model" in Prod and "Simple Recovery Model" in dev and test. But you have to identify your particular needs and make the decision yourself.
The safest way to 'shrink' transaction logs is to manage your backups so that you never have to explicitly do so.
Read up on recovery models and feel free to ask follow up questions.