SQL Server日志文件混乱 [英] SQL Server Log File Confusion

问题描述

我正在寻找有关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.

我的问题:

是什么原因导致数据库日志文件被刷新? 冲洗"实际上是什么意思? 在大型日志文件上执行文件收缩或数据库收缩会有什么后果?

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?

推荐答案

备份事务日志后，这些事务将从日志中被截断，但是该操作使用的空间不会自动恢复.如果您要进行常规的事务日志备份，这可能是一件好事.假设该空间用于交易，并且将来会再次需要.不断缩小事务日志可能会降低性能，因为再次需要时，数据库将需要扩展日志.

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.

因此，要解决您的问题，请先进行完整备份和事务日志备份.您无需进入简单"模式或单用户.然后收缩数据库，每隔几个小时设置一次事务日志备份.在我的服务器上，我每10分钟执行一次，但这完全取决于一个人对其环境的需求频率.监视日志大小的变化，确保留有足够的空间，以免日志有规律地扩展，另外还有一些额外的乐趣.

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.

缩小数据库时，请使用 DBCC SHRINKFILE 而不是 DBCC SHRINKDATABASE ，因为后者将收缩整个数据库，而不仅仅是收缩日志文件.另外，您对要恢复的空间没有太多控制.

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.

这篇关于SQL Server日志文件混乱的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！