你的位置:首页 > 软件开发 > 数据库 > SQL Server中的事务日志管理(7/9):处理日志过度增长

SQL Server中的事务日志管理(7/9):处理日志过度增长

发布时间:2015-11-10 09:00:05
当一切正常时,没有必要特别留意什么是事务日志,它是如何工作的。你只要确保每个数据库都有正确的备份。当出现问题时,事务日志的理解对于采取修正操作是重要的,尤其在需要紧急恢复数据库到指定点时。这系列文章会告诉你每个DBA应该知道的具体细节。这篇文章会列出导致事务日志过度增长的常见的问 ...

SQL Server中的事务日志管理(7/9):处理日志过度增长

当一切正常时,没有必要特别留意什么是事务日志,它是如何工作的。你只要确保每个数据库都有正确的备份。当出现问题时,事务日志的理解对于采取修正操作是重要的,尤其在需要紧急恢复数据库到指定点时。这系列文章会告诉你每个DBA应该知道的具体细节。


这篇文章会列出导致事务日志过度增长的常见的问题和错误管理形式,包括:

  • 在完整恢复模式里,没有进行日志备份
  • 进行索引维护
  • 长时间运行或未提交的事务阻止事务日志里空间重用

当然,如果增长没检查,日志文件会扩展直到吞没所有可用磁盘空间或日志文件的最大大小,在这个时候你会收到该死的9002错误(事务日志已满),数据库会变成只读。这篇文章会谈到处理日志不断增长和9002错误的正确方法,还会解释下通常建议截断或收缩事务日志为什么是危险的。

最后,我们会谈下保证你日志文件是平稳和可预见增长的措施,还有日志碎片的最小化问题。在忙碌的数据库里,大型的事务日志会是一个简单的生活事实,如果管理妥当的话,这并不是件坏事,即使大部分时间日志文件空间不可用。

日志大小和增长

任何时候日志文件需要增长,额外的空间被分配,这个空间平均分到VLS里,基于被分配空间数。

例如,日志文件默认会有2MB的初始大小,10%的自动增长率(来自model数据库的配置)。这就是说,日志文件开始至少会很小的增长,因此会有大量的小VLF。

当我们在很大的块上分配额外空间时,例如当一次操作初始16GB的大小,结果事务日志会有很小数量的VLF。

太高数量的小VLF,这样的情况称为日志文件碎片,会影响到性能,尤其在故障恢复,还原和备份,特别是日志备份。换句话说,它会影响读取日志文件的操作性能。我们会在第8篇详细讨论这个问题。

事务日志VLF——太多还是太少?

SQL Server MVP的Kimberly Tripp在他的文章里讨论了VLF大小的影响,并提供了如何恰当管理VLF大小的指导——事务日志VLF—太多还是太少?

相反,如果日志文件只有几个很大的VLF,我们有长时间占用大块日志的风险。每个VLF都有很大数量的日志记录,SQL Server不能截断VLF直到它没有包含活动日志。这个情况下截断会因某些原因延迟(在缺少日志空间重用部分会详细谈到),这会导致日志的快速增长。例如,我们假设每个VLF是1GB大小且日志满了。你进行了一次日志备份,但是所有的VLF包含活动日志的一部分,SQL Server不能截断日志。它没有别的选择只能增加更多VLF,如果日志的增长率设置为同等大小,那么日志增长会很快,直到有VLF变成可截断。

因此,正确设置日志初始大小非常重要,那它的增长才会是合适的大小步骤,最小化日志碎片也避免了过快增长。

正确设置初始大小且可控制它的增长的第2个原因是:对于日志文件,每个增长是相对昂贵的操作。数据和日志文件增长超时是正常的。SQL Server可以优化增加新数据文件和扩展现有数据文件的过程,通过即时文件初始化(instant file initialization)(在SQL Server 2005引入,允许在磁盘上分配空间给数据文件,而不需要进行填零)。遗憾的是,对于日志文件是不一样的,对于日志文件创建或增长的空间分配,还是需要初始化且填零。

为什么事务日志不能使用即时初始化?

进一步关注事务日志填零,看下Paul Randal的文章:http://sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-24-Why-cant-the-transaction-log-use-instant-initialization.aspx

事务日志不断增长的诊断

如果你经历事务日志的不可控增长,它由于要么是活动日志频率太高,要么是有因素阻止日志文件里的空间重用,或者两者都有。

如果增长的主要原因是活动日志过多,你要检查下是否可以避免这个活动,例如调整处理大容量数据和索引维护的数据库模式,这样的话这些操作不会完整记录(例如针对这些操作使用大容量日志恢复模式)。但是,如果日志备份里包含有任何的最小化日志操作,大容量操作会立即阻止数据库到时间点的恢复(可以阅读下第6篇文章来获得更多详细信息)。如果这是不可接受的,你必须直接接受大日志的事实,根据具体情况计划它的增长和管理(例如日志备份频率),在接下来的妥当的日志管理会介绍。

如果增长原因是缺少日志空间的重用,你要找出什么阻止这个重用并采取措施来修正这个问题。

日志过度增长:索引维护操作

索引维护操作是个很常见的导致事务日志使用率过度和增长的原因,尤其数据库运行在完整恢复模式里。进行索引维护需要日志空间量取决于下列因素:

  • 重建还是重组——通常索引重建在日志里会使用更多的空间
  • 恢复模式——如果时间点恢复的风险已理解且可接受的,那么索引重建可以临时通过切换到大容量恢复模式里的最小化日志。但索引重组始终是完整记录的。

索引重建

当重建索引时,不管在线还是离线,使用ALTER INDEX REBUILD,或是已经废弃的SQL Server 2000里的DBCC DBREINDEX,SQL Server新创建一个索引的副本,然后一旦重建完整,删除老的副本(这是为什么你至少需要数据文件里索引大小一样的可用空间)

日志记录和在线索引重建

在SQL Server 2008和后续版本,在线索引重建是完整日志操作,在SQL Server 2005里是最小化日志。因此,在后续SQL Server版本上进行这样的操作本质上需要更多的事务日志空间,可以看下:https://support.microsoft.com/zh-cn/kb/2407439 还有Kalen Delaney的日志,对于完整和大容量日志2个恢复模式,验证下在在线和离线索引重建期间的日志记录。

在完整恢复模式里,索引重建可以是非常占资源的操作,需要事务日志里的很多空间。在简单或大容量日志恢复模式里,重建索引是最小化日志操作,这意味着只有分配被记录,实际的页并没改变,因此通过这个操作减少了日志空间量。

如果你切换到简单模式进行索引重建,LSN链会立即中断。你只能恢复你的数据库到刚才事务日志备份里的包含的时间点。为了重新开始日志链,你需要切换回完整恢复模式并立即进行一次完整或差异数据库备份。

如果你切换到大容量日志模式(看下第6篇),LSN链还是连续的,但还会影响到进行时间点的恢复,因为包含最小化日志操作的日志备份不能用来恢复到时间点。如果能恢复到时间点的要求是至高无上的,那么索引重建或任何最小化日志操作不要使用大容量日志恢复模式。除非在数据库里没有同时发生的用户活动,你可以使用。不然的话,在可能的情况下考虑在完整恢复模式里进行索引重建。

如果使用的是大容量日志模式,尽可能使时间点恢复的时间最小,这样可以最小化暴露数据丢失风险。为了做到这一点,在完整模式里进行日志备份,切换到大容量日志,进行索引重建,然后切换回完整进行另一个日志备份。

最后一个重点要记住的是ALTER INDEX REBUILD操作是在一个单独事务里。如果索引很大,事务的执行时间会很长,在期间,这会阻止日志里的空间重用。这就是说,即使你在简单模式里重建索引,你也要想到自检查点(CHECKPOINT)操作后日志应该保持很小,重建是最小化日志,在剧烈的重建期间,日志文件还会快速扩展。

索引重组

和重建索引相比,使用ALTER INDEX REORGANIZE或者SQL Server里的DBCC INDEXDEFRAG(已废弃)重组(碎片整理)索引都是完整记录操作,不管是任何恢复模式,因此实际的页修改总被记录。但是,通常索引重组比索引重建需要更少的日志空间,尽管这是索引里降低碎片的一个功能;比起轻度碎片,重度碎片索引会需要更多的日志空间来重组。

另外,ALTER INDEX REORGANIZE操作是通过多个更短的事务完成的。因此,当与定期的日志备份相结合(或在简单恢复模式里)时,在此操作期间,日志空间可以被重用,因此要求操作期间日志空间最小化。

例如,对于重建操作,重建20GB的索引会需要超过20GB的空间,因为它发生在一个单独的事务里。但是,重组20GB的索引会需要更少的空间,因为在重组里每个页分配修改是个单独的事务,因此日志记录可以用定期日志备份截断,让日式空间可以重用。

控制日志过度措施

如果你的组织对任何潜在数据丢失不能容忍的,那么你没有选择,只能让所有的数据库运行在完整恢复模式里,并且妥当计划你的日志大小和增长。因此索引重建是作为一个单独线程发生的,日志至少会和你重建的索引一样的大小。如刚才所说,索引重组会需要更少的空间,且允许在操作期间通过日志备份来截断日志。这样的话,为了同时避免日志暴涨,可行的话,你可以用日志重组。

如果你的SLA和操作级别协议(Operational Level Agreements(OLAs))允许一些潜在的数据丢失,那么在索引重建前l切换到大容量日志恢复模式可以为重建索引最小化空间需求量。但是,要在最小化数据丢失的方式下进行,例如已经讨论确认过了。

不管使用的恢复模式,你可以通过重组索引而不是重建索引来在日志上最小化索引维护操作的影响。可以的话,可以看下微软的指导方针,为了最小化索引维护操作的影响,对于绝大数情况,并不是所有情况,决定什么时候进行索引重建,什么时候进行索引重组(查看索引重组和重建)。他们也声明:对于碎片级别大于5%且小于30%,你应该重组索引,对于碎片级别大于30%,你应该重建它。

但是,在索引维护期间,在保护日志过度增长里,最有效的武器是维护那些真正需要的索引。使用SSMS维护计划向导,索引维护是个孤注一掷的操作:要么重建(或重组)数据库里(维护计划里的所有数据库)的所有索引,要么全不维护。一个更好的方法是使用sys.dm_db_index_physical_stats的DMV来看下碎片程度根据需要来决定索引重建/重组策略。

Ola Hallengren的免费维护脚本

Ola Hallengren提供一个综合的免费维护工具,它展示了如何使用sys.dm_db_index_physical_stats进行索引分析来进行智能维护,它可以用来代替SSMS里向导创建的数据库维护计划(https://ola.hallengren.com/)。

但是最好的方法,是计划只维护那些可以在查询上提供真正持久影响的索引。逻辑碎片(在乱序中的索引页)挫败了SQL Server的预读机制(https://msdn.microsoft.com/zh-cn/library/ms191475%28v=sql.105%29.aspx),且使在磁盘上读取连续页I/O-效率更低。但是,这只真正影响从磁盘的大范围扫描。即使对非常大碎片的索引,如果你不扫描表,重建或重组索引不会提高性能。降低页深度(通过页分裂或删除造成的很多缺口)会带来更多的页占用磁盘空间,且在内存里,会需要更多的I/O带宽来传输数据。再说一次,这个碎片格式不会真正影响不频繁修改的索引,因此重建它们不会有帮助。

计划索引维护前,问下自己什么性能标准从维护受益?它会大会减少I/O?它会提高你最昂贵查询的多少性能?它是持久正面影响么?如果这些答案是“否”或“不知道”,那么定期索引维护可能不是个长远的答案。最后,值得注意的是对小索引维护是不值得的。通常引用的阈值是近1000页。在微软,当Paul Randal管理存储引擎开发团队时,建议这些值作为参考,在在线帮助里记录了。注意,尽管这只是个建议并不对所有环境合适,如Paul在他的博客文章里谈到的:“在线帮助的索引碎片阈值来自哪里?”

调查重日志写入事务

sys.dm_tran_database_transactions的DMV提供在事务日志上事务活动影响的有用内部信息。在他们的书里,《使用SQL Server动态管理视图进行性能调优》,得到他们的允许后,复制在这里,作者Louis Davidson和Tim Ford,演示了如何使用这个DMV和一些其他的,来调查可能造成事务日志过度增长的事务。

在代码7.1里的例子重用来自第6篇的FullRecovery数据库和PrimaryTable_Large表。在一个显性事务里,它重建了聚集索引然后调查日志增长。

 1 USE FullRecovery 2 GO 3 BEGIN TRANSACTION  4  5 ALTER INDEX ALL ON dbo.PrimaryTable_Large REBUILD 6  7 SELECT DTST.[session_id],  8 DES.[login_name] AS [Login Name],  9 DB_NAME (DTDT.database_id) AS [Database], 10 DTDT.[database_transaction_begin_time] AS [Begin Time], 11 DATEDIFF(ms, DTDT.[database_transaction_begin_time], GETDATE())12                         AS [Duration ms] ,13 CASE DTAT.transaction_type 14  WHEN 1 THEN 'Read/write' 15   WHEN 2 THEN 'Read-only' 16   WHEN 3 THEN 'System' 17   WHEN 4 THEN 'Distributed' 18  END AS [Transaction Type], 19  CASE DTAT.transaction_state 20   WHEN 0 THEN 'Not fully initialized' 21   WHEN 1 THEN 'Initialized, not started' 22   WHEN 2 THEN 'Active' 23   WHEN 3 THEN 'Ended' 24   WHEN 4 THEN 'Commit initiated' 25   WHEN 5 THEN 'Prepared, awaiting resolution' 26   WHEN 6 THEN 'Committed' 27   WHEN 7 THEN 'Rolling back' 28   WHEN 8 THEN 'Rolled back' 29  END AS [Transaction State], 30 DTDT.[database_transaction_log_record_count] AS [Log Records], 31 DTDT.[database_transaction_log_bytes_used] AS [Log Bytes Used], 32 DTDT.[database_transaction_log_bytes_reserved] AS [Log Bytes RSVPd], 33 DEST.[text] AS [Last Transaction Text], 34 DEQP.[query_plan] AS [Last Query Plan] 35 FROM sys.dm_tran_database_transactions DTDT 36 INNER JOIN sys.dm_tran_session_transactions DTST 37  ON DTST.[transaction_id] = DTDT.[transaction_id] 38 INNER JOIN sys.[dm_tran_active_transactions] DTAT 39  ON DTST.[transaction_id] = DTAT.[transaction_id] 40 INNER JOIN sys.[dm_exec_sessions] DES 41  ON DES.[session_id] = DTST.[session_id] 42 INNER JOIN sys.dm_exec_connections DEC 43  ON DEC.[session_id] = DTST.[session_id] 44 LEFT JOIN sys.dm_exec_requests DER 45  ON DER.[session_id] = DTST.[session_id] 46 CROSS APPLY sys.dm_exec_sql_text (DEC.[most_recent_sql_handle]) AS DEST 47 OUTER APPLY sys.dm_exec_query_plan (DER.[plan_handle]) AS DEQP 48 WHERE  DB_NAME(DTDT.database_id) = 'FullRecovery'49 ORDER BY DTDT.[database_transaction_log_bytes_used] DESC;50 -- ORDER BY [Duration ms] DESC;51 COMMIT TRANSACTION

原标题:SQL Server中的事务日志管理(7/9):处理日志过度增长

关键词:sql

sql
*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。