SQL SERVER-日志增长原因定位

SQL SERVER-日志增长原因定位

以下脚本第一句是查询整个实例各数据库日志文件的使用情况，

第二句是查询各数据库的恢复模式和不能截断的原因

DBCC SQLPERF(LOGSPACE)

SELECT name,recovery_model_desc,log_reuse_wait,log_reuse_wait_desc
	FROM sys.databases

各原因的解释如下：

Reuse of transaction log space is currently waiting on one of the following as of the last checkpoint. For more detailed explanations of these values, see The Transaction Log.Value
0 = Nothing
1 = Checkpoint (When a database uses a recovery model and has a memory-optimized data filegroup, you should expect to see the log_reuse_wait column indicate checkpoint or xtp_checkpoint) 1
2 = Log Backup 1
3 = Active backup or restore 1
4 = Active transaction 1
5 = Database mirroring 1
6 = Replication 1
7 = Database snapshot creation 1
8 = Log scan
9 = An Always On Availability Groups secondary replica is applying transaction log records of this database to a corresponding secondary database. 2
9 = Other (Transient) 3
10 = For internal use only 2
11 = For internal use only 2
12 = For internal use only 2
13 = Oldest page 2
14 = Other 2
16 = XTP_CHECKPOINT (When a database uses a recovery model and has a memory-optimized data filegroup, you should expect to see the log_reuse_wait column indicate checkpoint or xtp_checkpoint) 4

1 Applies to: SQL Server (starting with SQL Server 2008)
2 Applies to: SQL Server (starting with SQL Server 2012 (11.x))
3 Applies to: SQL Server (up to, and including SQL Server 2008 R2)
4 Applies to: SQL Server (starting with SQL Server 2014 (12.x))