为什么 SQL Server 似乎如此重视事务日志
好的，就这样吧。我不是数据库专家或管理员。事实上，除了偶尔进行一些索引/查询调整之外，我并不会经常研究数据库。我经常困惑的事情之一是 SQL Server 事务日志。我知道它的用途、它包含什么以及它如何工作（至少在概念上），但我想我不明白为什么 SQL Server 似乎如此依赖于事务日志。
这是第一个问题。如果我错了，请纠正我，但在我看来，默认情况下事务日志将仅包含数据库中所有更改的完整历史记录。有两个迹象表明情况可能确实如此。当我创建新数据库时，其日志的最大大小设置为“无限制增长”。第二个原因是我经常处理带有大量事务日志的小型数据库，无论我做什么都无法缩小。这似乎太奇怪了，我简直不敢相信这是真的。为什么我默认想要整个历史记录？我所关心的是处于一致状态的最新版本的数据。好吧，我怀疑在某些情况下可能有正当理由，但我会认为这是一个额外的选择。
我的第二个问题是为什么摆脱转换日志如此复杂？是只有我一个人这样，还是真的没有直接的办法呢？就在最近，我试图摆脱 5MB 数据库的 100MB+ 日志，我发现最简单的方法是分离数据库，删除日志并重新附加它（甚至 SQL Serve 也抱怨了一点）。我尝试了收缩命令以及我能找到的所有可能的选项，但我只能收缩到大约 50%。数据库没有在使用中（没有活动连接），老实说我根本不关心任何过去的转换。我注意到可能还有其他一些“方法”可以做到这一点；有些涉及备份和恢复。
我努力地尝试阅读 MSDN 文档并了解更多有关转换的知识，但大约 15 分钟后，我感觉就像在泥浆中兜圈子一样，我放弃了。我知道对于数据库管理员和专家来说，我的问题听起来很愚蠢。我很感激任何反馈。
编辑：在第一个答案之后，我意识到我可能还不够清楚。我知道事务日志在事务期间如何工作，为什么它很重要，并且它可以用于备份目的。我想我想从开发者的角度问更多问题。大多数时候，我处理临时/测试临时数据库，这些数据库不需要任何备份，除了我之外没有人使用，而且我经常发现自己需要传输它，并且在这种情况下拥有巨大的过渡日志会带来不必要的不便。
OK, here is goes. I’m not a database guru or admin. In fact, besides some occasional index / query tuning, I don’t poke around too often in databases. One of the things which often eludes me is the SQL Server transaction log. I know what it’s for, what it contains and how it works (at least conceptually), but I think I don’t get why SQL Server seems to be so attached to transaction logs.
Here is the first question. Correct me if I’m wrong, but it seems to me by default transaction logs will simply contains the entire history of all changes in the database. There are two indications that this is may be indeed the case. When I create a new database, the maximum size of its log is set to "unrestricted growth". The second reason is that I have often dealt with tiny databases with huge transaction logs which could not be shrunk no matter what I did. It seems to be so odd I cannot believe it’s true. Why would I ever want the entire history by default? All I care about is the latest version of the data in a consistent state. Well, I suspect that there may be valid reasons for it is some cases, but I would consider this as an extra option.
My second question is why is so complicated to get rid of a transition log? Is it just me, or is there really no direct way to do it? Just recently, I was trying to get rid of a 100MB+ log of a 5MB database, and the simplest way I found was to detach the database, delete the log and re-attach it again (and even that SQL Serve complained a bit). I tried the shrink command with all possible options I could find, but I was able to shrink in only to about 50%. The database was not in use (no active connections), and I honestly did not care about any past transitions at all. I noticed that there are possibly some other "ways" how to do it; some involving backups and restores.
I diligently tried to read the MSDN documentation and learn something more about transitions, but after about 15 minutes which felt like walking in mud in circles, I gave up. I know that to database admins and gurus my questions will sound silly. I appreciate any feedback.
Edit: After the first answers, I realized that I may not been clear enough. I’m aware how a transaction log works during transactions, and why it’s important, and that it can be used for backup purposes. I think I wanted to ask more from the developer point of view. Most of the time I deal with staging / test temporary databases which don’t need any backup, and which nobody is using except me, and I often find myself needing to transfer it and having a huge transitional log is an unnecessary inconvenience at that situation.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
数据库日志不是一些事后历史记录，如 IIS 日志。在具有预写日志记录的数据库中，日志是主要恢复、重做和撤消源，并且所有目的都是权威数据来源。删除或替换日志是管理数据库的人可以做出的最糟糕的决定之一。此时您的数据库可能已损坏。
截断日志的正确方法是先进行完整数据库备份，然后进行数据库日志备份，最后进行定期日志备份。这将释放日志中的空间并允许其重用。它不会收缩物理 LDF 文件。
另一种途径是将恢复模式更改为SIMPLE，这将允许服务器根据需要自动回收日志文件。将恢复模型更改为 SIMPLE 会对数据库的可恢复性产生影响，因为您将无法应用某些灾难恢复方案（例如时间点恢复），也无法恢复自上次备份以来的任何数据更改。
了解 SQL Server 中的日志记录和恢复
关于日志和日志备份的误解：如何说服自己
存储引擎内部：有关日志循环性质的更多信息
The database log is not some after the fact history record, like an IIS log. In databases with Write-Ahead Logging the log is the primary restore, redo and undo source, and for all purposes the authoritative source of data. Deleting or replacing the log is one of the worst decisions somebody administering a database can do. Your database may be corrupted at this moment.
The proper way to truncate the log is to do a full database backup, followed by a database log backup, followed by periodical log backups. This will free space in the log and allow for its reuse. It will not shrink the physical LDF file.
Another avenue is to change the recovery model to SIMPLE, which will allow the server to automatically recycle the log file as it sees fit. Changing the recovery model to SIMPLE has implications on the recoverability of the database as you won't be able to apply certain disaster recovery scenarios like point in time recovery nor will you be able to recover any data changes since last backup.
Understanding Logging and Recovery in SQL Server
Misconceptions around the log and log backups: how to convince yourself
Inside the Storage Engine: More on the circular nature of the log
事务日志的存在有两个主要原因
1 - 允许数据库“回滚”当前事务
2 - 允许崩溃的数据库恢复到崩溃前最后提交的事务的状态
1 是暂时的，如果没有活动事务，那么它将消耗很少的空间。 2 然而，正如您猜测的那样，随着时间的推移，它会继续增长，甚至可能没有限制。
这个想法是您经常将事务日志备份到转储文件中。转储文件是数据库的完整状态快照，您可以从中恢复某个时间点的整个数据库。当您执行此操作时，事务日志将被截断并从空重新开始。
如果数据库随后崩溃，您可以从转储文件加载数据库快照，然后应用事务日志（其中包含自上次快照以来的增量更改）来恢复状态。因此，事务日志需要保存在高恢复能力的存储中，否则您将无法恢复数据库。
因此，通过定期进行数据库转储，您的事务日志将被反复清除。
还有另一个选项（我不推荐），即禁用数据库上的事务日志。这基本上消除了上面的 2 个问题，因此事务日志不会随着时间的推移而增长。当然，如果您的数据库崩溃，您只能从上次完整备份/转储中恢复，因此如果您正在运行任何半重要的内容，则此选项会带来相当大的风险。
HTH。
Transaction logs exist for two main reasons
1 - To allow the DB to "rollback" a current transaction
2 - To allow a crashed DB to recover it's state up to the last committed transaction before the crash
1 is transient, if you have no active transactions then it will be consuming little space. 2 however, as you surmise continues to grow over time, maybe without bound.
The idea is that you backup the transaction log every so often to a dump file. A dump file is a complete state snapshot of a DB that you can recover the ENTIRE DB from at a point in time. When you do this the transaction log is truncated and starts again from empty.
If the DB subsequently crashes you load the DB snapshot from the dump file and then apply the transaction log, which contains the incremental changes since the last snapshot, on top to recover the state. For this reason transaction logs need to be kept on high resiliance storage otherwise you can't recover the DB.
So by taking regular db dumps your transaction log is repeatedly cleared down.
There is another option, which I wouldn't recommend, which is to disable transaction logs on your DB. This basically eliminates 2 above so the transaction log doesn't grow over time. Of course if your DB crashes you can only recover from the last full backup/dump so this option carries considerable risk if you are running anything even semi-important.
HTH.
您对日志中包含或不包含的内容有一些不正确的想法，根据您的恢复模式，日志可能仅包含当前未提交的事务，因此它们可能会回滚（简单模式）或可能仅包含最少记录的操作，但生成的事务日志备份会更大，因为它们将包含日志备份之间更改的范围（批量日志模式），或者可能包含自上次日志备份以来每个已提交事务的所有日志信息。
无限制增长只是默认情况，让日志任意增长并不是最佳实践，因为这会增加实际日志文件中 vlf（虚拟日志文件）的数量，从而导致日志碎片。获得太多这些后，某些操作（例如复制）的性能就会受到影响。
如果日志文件大于数据库并且备份后无法收缩，则系统上可能存在保存 VLF activem 的打开事务，这会阻止其重新使用空间。
分离数据库并在杀死日志后重新附加它对数据库来说是一件非常糟糕的事情，它无法处于事务一致状态，并且您确实希望在执行此操作后运行 dbcc checkdb。是的，您没有活动连接，但这是一件有风险的事情，而且当您发生日志损坏时，这几乎是最后的手段，而不是列表上的第一件事。
这被认为是很糟糕的，在 2005 年甚至有一个你不能改变的标志，表明你已经做到了。 MS 支持人员可以检查您是否需要他们帮助解决 SQL 中的潜在错误。
就摆脱日志而言，正是日志使 DBA 能够提供恢复策略，可以将数据库恢复到硬件故障/损坏破坏数据库的近乎精确的时间点。如果没有事务日志，您将只能恢复到上次完整备份。通过日志，您可以像快进录像机一样重播交易。
正如您所看到的，这个主题非常大并且很复杂，但它值得学习。
You have a couple of incorrect thoughts on what is or is not within the logs, depending on your recovery mode the log may only contain the current uncommitted transactions, so they may be rolled back (simple mode) or might contain only minimally logged operations, but the resulting transaction log backups will be larger, since they will contain the extents altered between the log backups (bulk logged mode) or could contain all the log information for each committed transacation since the last log backup.
The unrestricted growth is just a default and it not best practise to let a log just arbitarily grow, since this causes log fragmentation by increasing the number of vlf, virtual log files within the actual log file. After you get too many of those, performance of certain operations, such as replication suffers.
In the case where the log file is larger than the database and after a backup is unable to be shrunk, then there is potentially an open transaction on the system holding the VLF activem which is preventing it from re-using the space.
Dettaching the database and reattaching it after killing the log is a pretty bad thing to do to your database, it could not be in a transactionally consistent state and you really want to run a dbcc checkdb after doing that. Yes, you had no active connections but it is a risky thing to do and pretty much a last resort when you have had log corruption, not a first thing on the list.
It is considered that bad, that within 2005 there is even a flag you can not alter which indicates you have done it. The MS support can check if you were after their help with a potential bug in SQL.
In terms of getting rid of the log, it is the log that enables a DBA to provide a restore strategy that can restore a database to the near exact point in time that a hardware failure / corruption destroyed the database. Without the transaction log you would only be able to recover to the last full backup. With the logs you can replay the transactions like a fast forwarding video recorder.
The topic is very large and as you have seen complex, but it is worth learning about.
（这是 MS-SQL，对吧？）
是的，我敢打赌，当您手动分离和删除日志时，服务器确实会抱怨。
如果您不想保留 T 日志，请将目录的恢复模式更改为“简单”（在目录的属性中），这将在事务成功提交时截断日志。
如果您确实需要日志（大多数人都需要，拥有它们非常方便！），日志在备份时将被截断。
（交易日志，FTW！）
(This is MS-SQL, right?)
Yeah, I bet the server did complain when you detached and deleted the log manually.
If you don't care to keep a T-log, change the catalog's recovery mode to "Simple" (in catalog's properties) this will truncate the log as transactions are committed successfully.
If you DO want the log (most people do, they are quite handy to have!) the log will be truncated when it is backed up.
(Transaction logs, FTW!)
大多数关系数据库服务器都维护一个事务日志，记录对数据库所做的所有更改。这个想法是，如果数据库服务器出现故障，您可以“重放”上次数据库备份中对数据库所做的所有更改，这应该是已知状态。
自从我使用 SQL Server 以来已经有一段时间了，所以我无法回答为什么摆脱事务日志如此困难。在SQL Server 7、SQL Server 2000天中，默认情况下对数据库进行完整备份会截断事务日志。
Most relational database servers maintain a transaction log that records all the changes made to the database. The idea is that if the database server goes down, you can "replay" all the changes made to the database from the last database backup, which should be a known state.
It has been a while since I worked with SQL Server, so I can't answer why it's so hard to get rid of a transaction log. In the SQL Server 7, SQL server 2000 days, taking a full backup of the database would truncate the transaction log by default.