《SQL Server企业级平台管理实践》读书笔记——SQL Server数据库日志增长的原因解析
数据库日志用于记录所有的事务以及每个事务对数据库所做的修改。这样做的目的就是为了提高数据库的整体性能，SQL Server检索数据时，将数据页读入缓冲区高速缓存。数据修改不是直接在磁盘上行进行，而是修改高速缓存中的页副本。直到数据库中出现检查点（CheckPoint）、或者比如将修改写入磁盘才能使用缓冲区来容纳新页时，才能将修改写入磁盘。将修改后的数据页从高速缓冲存储器写入磁盘的操作称为刷新页，而高速缓存中修改尚未写入磁盘的页称为：“脏页”。
对缓冲区中的页进行修改时，会在日志高速缓存中生成一条日志记录。SQL Server具有防止在写入关联的日志记录前刷新脏页的逻辑。会确保日志记录在提交事务时，或者在此之前，一定已经被写入磁盘。
SQL Server对数据页的插入、修改和删除，都是只在内存中完成后，就提交事务。这些修改并不立刻同步到磁盘的数据页上。而SQL Server又必须保证事务的一致性。哪怕发生了SQL Server异常终止（例如SQL Server服务崩溃，机器断电），内存中的修改没有来得及写入磁盘，下次SQL Server重启的时候，要能恢复到一个事务一致的时间点。已经提交的修改要在磁盘中的页面重新完成。为了做到这一点，SQL Server必须依赖事务日志。
因此，任何一个数据库，哪怕是tempdb，都有日志文件。它和数据文件同等重要。如果日志文件缺失或者损坏，后果等同于数据库损坏。
在SQL Server的使用过程中，会由于种种原因，出现日志文件大小不停增长的现象。当文件达到最大限制，或者是把硬盘空间全部占用后，数据库就无法再进行任何插入、修改、删除的工作。下面我们列出日志增长的原因：
我们先来看看数据日志内容有哪些：
use TestDB go create table a (a int) go checkpoint go backup log TestDB with NO_TRUNCATE go --查看数据库明细信息 sp_helpdb go --20是TestDB的数据库编号，3是查阅日志的明细内容 dbcc log(20,3) go --我们插入一条数据记录 insert into a values(1) go dbcc log(20,3) go
能看到日志明细：
我们可以看到日志链、操作、执行内容、起始时间、SPID等吧，具体信息可参照桦仔的博客：解释一下SQLSERVER事务日志记录
--查看日志也可以用这个语句，执行结果和DBCC Log(20,3)的执行结果一致 select * from [sys].[fn_dblog](NULL,NULL) go
我们再来看一下更新数据的操作：
update a set a=2 go dbcc log(20,3) go
同样日志又增加了四条记录：
其实可以看到一个简单的Update或者Delete语句，就事务日志本身而言，他会产生多条记录来记录原数据值和修改后的值，便于回滚和提交。
SQL Srver的日志记录有以下特点：
1、日志记录的是数据的变化，而不是记录用户发过来的操作。
只是记录数据本身的变化，不能使用它来推测出用户发过来的语句内容
2、每条记录都有它唯一的编号（LSN），并且记录了它属于的事务号。
便于数据的提交和回滚
3、日志记录的行数和实际修改的数据量有关，和语句内容的多少无关
SQL Server会为每一条记录的修改保存日志记录。如果单个语句修改的行数非常多，那么它所带来的日志行数也会非常多。所以日志增长的速度不仅和事务的多少有关，还和事务所带来的数据的修改量有关。
4、日志记录事务的发生的时间，但是不保证记录下了发起这个事务的用户名，更不记录发起程序的名称
5、SQL Server能够吃那个日志记录里面读到数据修改前后的值，但是对管理者来讲，直接从日志记录里面是很难了解其修改过程的。
讨论这些原因，是因为很多用户希望能从日志文件里推到出数据库曾将发生的异常操作。比如：是谁恶意或不下心删除了一些重要数据，或者是谁某个时间段发起了一个庞大的事务。由于SQL Server日志定位不是做用户行为的监视和记录，而是对性能影响最小的前提下保证事务的一致性，所以它记录的内容是面向数据库服务的，而不是考虑用户去访问好理解。所以使用者很难用事务日志来达到推到的目的。
其实SQL Server会为所有的修改记录日志，以便将来重新提交或者回滚使用。不停的记录就导致了文件的增长，空间的不足，但是SQL Server设计了相应的机制，能够定期清理日志文件中不需要的日志记录。
但有一部分日志SQL Server认为他是需要的，该部分日志记录记录的内容有哪些呢？
1、所有没有经过“检查点”的日志记录。
SQL Server定期做检查点（checkpoint)，保证所有的“脏页”都被写入硬盘。未做检查点的修改，可能仅是内存中的修改。数据文件里还没有同步。SQL Server要硬盘上的日志文件里有一份记录，以便在异常重启后重新修改。
2、所有没有提交的事务所产生的日志记录，以及他们之后的所有的日志记录。
如果一个事务没有提交，那他可以在任何时候回滚。SQL Server必须做好这种准备，以便能够从根日志记录中找回修改以前的内容，完成回滚。在SQL Server里面，所有的日志记录都有严格顺序，中间不可以任何跳跃。所有如果某个数据库有没有提交的事务，SQL Server会标记所有从这个事务开始的日志记录（不管和这个事务有没有关系）为活动事务日志。这些日志记录都有可能“需要”被用来做回滚。
3、所有要做备份的日志记录
如果数据库设的恢复模式不是简单模式，那SQL Server就假设用户是要去备份日志记录的。所有为备份的记录，SQL Server都会为用户保留，哪怕这些记录对数据库本身没有其它用途了。
4、其他需要读取日志的数据库功能模块
事务性复制和数据库镜像也需要读取日志中内容、完成他们的同步工作。在这些功能组件没有读取日志记录以前，SQL Server也会保留。
对所有“不需要”的日志记录，SQL Server会在每个检查点做一次截断的动作，把这些记录占用的空间标志成可重用。这样这些空间就被释放出来。因为日志文件是循环使用的，只要日志文件里有这样的空间，SQL Server都回去重用，所以不会报告空间已满，或者试图去做自动增长。SQL Server做检查点的频率取决于服务器属性“Recovery Interval”。默认大概一分钟左右一次检查点。
经过上面的分析，我们来总结下日志文件增长的原因有这几个：
1、数据库恢复模式不是简单模式，但是没有安排日志备份。
需要强调的是，对于非简单模式的数据库，只有做完日志备份后记录才会被截断。做完成备份和差异备份都不会起这个作用。
2、数据库上面有一个很长时间都没有提交的事务。
由于应用程序设计的问题，有些连接可能会遗留一个事务在SQL Server里面，而不是及时提交了它。SQL Server是不会干预用户的这种行为的。只要这个连接不退出，这个事务就会永远存在，知道客户端提交或者回滚它。而从这个事务开启的那个时间点开始的所有日志记录，SQL Server都会保留（做过日志备份也没有用，该日志永远存在！）
3、数据库上有一个很大的事务正在运行。
例如，某个用户正在建立/重建索引，或者用Delete/Insert语句删除或插入大量数据等。或者用户端开了一个服务端游标，但是没有把数据及时取走等。
4、数据库复制或者镜像出了异常。
要避免日志文件不停增长，其实就是要避免上面这些情况的发生，对于一个最近不会去做日志备份的数据库，设成简单恢复模式即可。如果数据库设成了完整恢复模式，那就一定要安排定期做日志备份。
如果复制或镜像出了问题，要及时解决。如果没有办法解决，也要避免事务时间过长，一个事务做太多的操作。数据库晚上或周末虎有一些维护工作，例如历史数据清洗整理，数据导入导出、重建索引等，这些操作都可能写许多日志，所以要为他们预留出足够的空间，并且在做完之后及时备份。
日志增长原因的定位
当日志文件增长很大时，可以采取一些临时手段，比如把有未关闭事务的连接强制取消，或者截断数据库的事务，但是，管理者必须找到日志增长的原因，从而根本上解决问题。
步骤一、检查日志现有使用情况和数据库状态
首先检查当前日志的百分比、数据库恢复模式和日志重用等待状态。我么利用SQL Server2005在管理视图：sys.databases里面加入了一列log_reuse_wait(log_reuse_wait_desc)，用SQL Server本身自己去诊断不能截断日志的原因。
| log_reuse_wait值 | log_reuse_wait_desc值 | 说明 |
| 0 | NOTHING | 正常，存在一个或多个可重用的虚拟日子文件 |
| 1 | CHECKPOINT | 自上次日志截断以后，尚未出现检查点，或者日志头部尚未跨一个虚拟日志文件移动范围（所有恢复模式） |
| 2 | LOG_BACKUP |
要求日志备份将日志标头前移（仅使用与完整恢复模式和大容量恢复模式）。日志备份完成后，日志标头将前移， 并且一些日志空间将变的可以重用 |
| 3 | ACTIVE_BACKUP_OR_RESTORE | 数据备份或还原正在进行（所有恢复模式）数据备份与活动事务的工作原理相同；数据备份运行时，将组织截断 |
| 4 | ACTIVE_TRANSACTION |
事务处于活动状态（所有恢复模式） 1、有一个用户开启了一个长时间运行的事务。在这种情况下，可能需要那个用户将事务提交或者回滚以后， 才能释放空间 2、事务被延迟，“延迟的事务”是有效的活动事务，因为某些资源不可用，其回滚受阻 |
| 5 | DATABASE_MIRROING | 数据库镜像暂停，或者在高性能模式下，镜像数据库明显滞后于主体数据库（仅限于完整恢复模式） |
| 6 | REPLICATION | 在事务复制过程中，与发布相关的事务仍未传递到分发数据库（仅限于完整恢复模式） |
| 7 | LOG_SCAN | 正在进行日志扫描（所有恢复模式） |
| 8 | OTHER_TANSIENT | 此值当前未使用 |
此过程我们执行下面脚本，检查：
DBCC SQLPERF(LOGSPACE) GO SELECT name,recovery_model_desc,log_reuse_wait,log_reuse_wait_desc FROM sys.databases GO
如果当前日志的绝大部分都在使用中，（Log Space Used(%)很高），那句要马上定位是什么原因导致了日志记录不能被SQL Server清除掉。如果当前日志的大部分都已经处于空闲状态了，那就说明触发日志增长的原因已经暂时消失。数据库现在的状态是正常的。如果问题反复发生，可能就要想办法跟踪SQL Server内部运行的操作，直到抓到问题的原因。
如果数据库的日志重用等待状态是LOG_BACKUP，那就意味着SQL Server在等待着日志备份。这时就需要检查备份计划，是否需要做日志备份。如果用户并不期望做日志备份，那就可以直接把恢复模式改成简单。这样SQL Server会在下一个检查点的时候做日志记录截断的工作。等到以后要安排日志备份任务的时候，再把恢复模式改过来。
步骤二、检查最老的活动事务
如果日志大部分都在使用中，而且日志重用的等待状态是ACTIVE_TRANSACTION，那么就要看这个数据库最久未提交的事务到底是由谁里申请的。我们用下面脚本查看
找到这个事务，比较推荐的方式是找到那个程序，从客户端提交或取消这个事务（比如，暂时终止这个用户正在进行的操作）。如果来不及或者一时找不到，可以直接KILL掉，当时，KILL命令并不是百试不爽的，如果一个连接正处于为提交或者回滚过程中，SQL Server会尊重它的执行而不去强行终止它。而如果需要终止的这个连接所开启的事务非常庞大，比如大数据删除（delete），哪儿取消掉这个动作而产生的回滚时间，可能不会比它的运行时间短。有时候可能会等很久这个连接也终止不掉。所以这个方法只能在应急的时候尝试使用，要从根本上解决问题，还是要改变客户端的行为，避免这种事情发生。
