经与日志截断的问题？

一般来说，如果你的数据库事务日志的比例增长了，那么你就需要更多的定期备份。这是假设你使用的是完整恢复模式，你首先备份日志。但是，即使你备份定期日志有可能的原因是，日志继续增长。让我们来看看。请记住，只有备份日志语句将截断事务日志。BACKUP DATABASE语句没有。这是一个普遍的误解，以DBA的新的SQL Server。截断手段丢弃已备份，事务日志中释放空间的交易。截断不会使.LDF文件较小。您需要DBCC SHRINKFILE为备份后。 However, even if you backup the log on a regular basis there may be reasons that the log continues to grow. 1. Active Transaction: A single long running transaction can limit a truncation operation. SQL Server cannot truncate beyond the start of the oldest currently active transaction. A long running transaction may be blocked because of locks. It may be a BULK INSERT that is not split up into batches using BATCHSIZE. It may be due to an application design flaw, where an application starts a transaction then opens a window to the user during the transaction. The user leaves the window open and voila – a long running transaction. To find out the oldest active transaction use DBCC OPENTRAN. 2. Replication: During transactional replication, transactions that are still undelivered to the distribution database will limit truncation. This has the effect of a long running transaction even though the transaction may have committed already. Or maybe you have the Log Reader Agent running to a schedule and that interval is too long. Again, to display the oldest non-distributed replicated transaction, use DBCC OPENTRAN. 3. Database Mirroring: Database mirroring has been paused, or under high-performance mode, the mirror database is significantly behind the principal database. This means that updates that are due to be applied to the mirror database are lagging and cannot be truncated. In this case, you may have to stop database mirroring, take a log backup that truncates the log, apply that log backup to the mirror database (using WITH NORECOVERY), and restart mirroring but that would be a last resort. You can find out exactly what is causing the truncation issue using sys.databases and a column called log_reuse_wait_desc . Here’s a good technical article on the subject. Factors That Can Delay Log Truncation: http://msdn.microsoft.com/en-us/library/ms345414.aspx Cheers Brian