SQL Server 日志文件混乱
我正在寻找有关 SQL Server 日志文件的一些说明。 我有一个较大的数据库（2GB），最近由于某种原因没有备份。 数据库的日志文件增长到大约 11GB，据我了解，这是数据库中发生的所有事务和语句。
我的问题：
是什么导致数据库日志文件被刷新？ “冲洗”实际上是什么意思？ 对大型日志文件执行文件收缩或数据库收缩会产生什么后果？
I'm looking for some clarity on the SQL Server log file. I have a largish database (2GB) which lately wasn't backed up for whatever reason. The log file for the database grew to around 11GB which from my understanding is all the transactions and statements that occurred in the database.
My questions:
What causes the database log file to be flushed?
What does "flush" actually mean?
What are the consequences of doing a file shrink or database shrink on a large log file?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
备份事务日志后，这些事务将从日志中截断，但操作使用的空间不会自动恢复。 如果您定期进行事务日志备份，这可能是一件好事。 假设该空间用于交易并且将来会再次需要。 持续缩小事务日志可能会降低性能，因为当再次需要时，数据库将需要扩展日志。
因此，要解决您的问题，请首先进行完整备份和事务日志备份。 您不需要进入简单模式或单用户。 然后收缩数据库并每隔几个小时设置一次事务日志备份。 在我的服务器上，我每 10 分钟执行一次，但这完全取决于他们的环境需要的频率。 监视日志大小的变化，确保留出足够的空间，使其不必定期扩展，再加上一点额外的乐趣。
当您缩小数据库时，请使用 DBCC SHRINKFILE 而不是 DBCC SHRINKDATABASE 因为后者将收缩整个数据库，而不仅仅是日志文件。 此外，您对恢复多少空间也没有太多控制权。
Once you backup the transaction log, those transactions are truncated from the log but the space used by the operation is not automatically recovered. If you are doing regular transaction log backups, this can be a good thing. The assumption being that the space being used for transactions and will be needed again in the future. Continuously shrinking the transaction log can be bad for performance since when needed again the database will need to expand the log.
So to fix your issue, first do a full backup and a transaction log backup. You don't need to go to Simple mode or single-user. Then shrink the database and set up a transaction log backup every few hours. On my servers I do it every 10 minutes, but it entirely depends how often one needs for their environment. Monitor how the log size changes, be sure to leave enough room such that it does not have to expand regularly, plus a little extra for fun.
When you do shrink the database use DBCC SHRINKFILE rather than DBCC SHRINKDATABASE since the latter will shrink the entire database, not just the log file. Also you don't have as much control over how much space is being recovered.
备份通常会清除事务日志。 事务日志保留自上次备份以来的所有更改。 根据您备份数据库的方式，您可能根本不需要保留完整的事务日志。 如果您使用的是 MS SQL2000/MS SQL2005，则将恢复模式设置为简单会消除事务日志。
一旦您确定拥有数据库的干净副本（没有丢失风险），就可以安全地删除事务日志。 有一些 SQL 命令可以执行此操作，但我通常将恢复模式更改为简单，然后收缩数据库，然后在必要时将恢复模式设置回完整。
如果您需要更多信息，请包括您正在使用的 SQL 版本以及执行备份的方式，我将看看是否可以详细说明您的具体设置。
A backup usually clears the transaction log. The transaction log keeps all changes since the last backup. Depending on how you backup the database you may not need to keep a full transaction log at all. If you are using MS SQL2000/MS SQL2005 setting the recovery mode to Simple does away with the transaction log.
Once you are sure you have a clean copy of the database (with no risk of loss) it is safe to remove the transaction log. There are some SQL commands to do it but I usually change the recovery mode to Simple then shrink the database then set the recovery mode back to Full if necessary.
If you need more info then include the version of SQL you are using and how you perform backups and I'll see if I can elaborate bit more on your specific set-up.
实际上，我不认为备份数据库会缩小日志，备份事务日志本身会缩小日志。 SQL Server 对两者进行了区分。
通常，我要么使所有数据库“简单”恢复，要么将备份事务日志作为我的定期维护计划的一部分（通常每周一次）。
Actually, I don't believe backing up the database shrinks the logs, backing up the transaction logs themselves shrinks it. SQL Server makes a distinction between the two.
Typically, I will either make all databases "Simple" recovery, or make backing up the transactions logs a part of my regular maintenance plan (normally once a week).