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

?

（代码7.1：调查重日志写入事务）

（插图7.1：索引重建后日志活动结果）

?

顺便提下，如果我们用ALTER INDEX...REORGANIZE来运行这个例子，那么在Log Bytes Used列的值会从近159M降为近0.5M。

?

缺少日志空间重用

?

如果你怀疑缺少日志空间重用造成了日志增长，你的第一个任务是找出什么阻止了重用。开始通过查询如代码7.2所示的sys.databases，看下对于提到的数据库log_reuse_wait_desc的列值错误信息是什么。

?

1 SELECT name ,

2 ? ? ? ? recovery_model_desc ,

3 ? ? ? ? log_reuse_wait_desc

4 ?FROM ? sys.databases

5 ?WHERE ?name = 'FullRecovery'

（代码7.2：检查下log_reuse_wait_desc的列值）

?

log_reuse_wait_desc的列值会展示为什么当前空间不被重用的原因。如果你已经执行刚才的例子（代码7.1），那么很可能FullRecovery数据库在这列会显示LOG_BACKUP值（下面会详谈）。

阻止日志重用不止一个。sys.databases视图只显示其中一个原因。因此它是解决问题的一个可能方法，再次查询sys.database会看到log_reuse_wait不同的原因。

?

在在线帮助里列出了log_reuse_wait_desc所有可能值，但在这里我们只谈最常见的原因，解释如何安全确保那个空间可以被重用。

?

没有日志备份的完整恢复模式

?

如果从sys.databases查询，log_reuse_wait_desc的返回值是LOG_BACKUP，那么你很可能遭受完整或大事务日志的最常见原因，即在完整恢复模式里的数据库（或次之，大容量日志恢复模式），没有进行事务日志备份。

?

在SQL Server的很多版本里，model数据库默认是完整恢复模式。因为model数据库是创建所有新SQL Server用户数据库的模板，新的数据库继承自model的配置。

?

对于大多数生产数据库，使用完整恢复模式是推荐的做法，因为它允许数据库的时间点恢复，最小化灾难事件的数据丢失。但是，接下来的常见错误是调整备份策略是只有完整备份（或者有差异备份）而没有定期的事务日志备份。这个策略有2个大问题：

?

进行完整数据库备份只保护数据文件内容，没有日志文件内容。完整保护已改变数据的唯一方法是自完整或差异备份后，需要时间点的恢复，是进行日志备份。

?

完整数据库备份不会截断事务日志。只有日志备份会造成日志文件截断。没有的话，日志文件里的空间是从不标记重用的，日志文件会不停的增长。

?

为了进行时间点的恢复并控制日志大小，我们必须用数据库完整或完整和差异备份连同事务日志备份。对于我们的FullRecovery数据库，我们可以进行日志备份，如代码7.3所示，然后再次查询sys.databases。

?

?1 USE master

?2 GO

?3 BACKUP LOG FullRecovery

?4 TO DISK = 'D:\SQLBackups\FullRecovery_log.trn'

?5 WITH INIT

?6 GO

?7?

?8 SELECT ?name ,

?9 ? ? ? ? recovery_model_desc ,

10 ? ? ? ? log_reuse_wait_desc

11 FROM ? ?sys.databases

12 WHERE ? name = 'FullRecovery'

（代码7.3：解决日志备份问题）

?