本文是在阅读《SQL Server Transaction Log Management》的Chapter 2: Log Internals时发现以往对Log Grows的理解比较片面，大部分内容引自原文，记录此文仅用于加深理解。
在讨论日志截断和空间重用时，我们会看到类似下面的结构图
A transaction log with 8 VLFs, after truncation
由于checkpoint(或者日志备份)，VLF1和VLF2被截断并处于非活动状态。现在逻辑日志的开始位置是VLF3。VLF8因为还未被使用，所以仍然处于非活动状态。
当活动日志到达VLF7的尾部时会发生什么？
最简单的情形，一旦日志的逻辑结尾到达一个VLF的尾部，SQLServer将会开始重用下一个非活动的VLF。在上图中，将会是VLF8。当VLF8被写满之后，将会环绕到VLF1和VLF2并且重用它们。
如果没有可用的VLF可以被重用，那么日志需要自动增长，并分配更多的VLF。如果由于自动增长被禁用或者物理磁盘空间被占满，那么活动日志的逻辑尾部将会是日志文件的物理尾部。此时事务日志就会被写满，触发9002错误。
How SQL Server Grows the LOG
一直以为SQLServer先重用非活动的VLF，然后再启用自动增长。实际并非如此！！！
首先创建测试数据库及数据表
/********** SQL Server Transaction Log Management->Chapter2 **********/
--Listing 2.1: Re-creating the TestDB database, plus PrimaryTable_Large
USE master ; IF DB_ID('TestDB') IS NOT NULL
DROP DATABASE TestDB ; CREATE DATABASE TestDB ON PRIMARY
( NAME = N'TestDB'
, FILENAME = N'D:\SQLData\TestDB.mdf'
, SIZE = 199680KB
, FILEGROWTH = 16384KB
)
LOG ON
( NAME = N'TestDB_log'
, FILENAME = N'D:\SQLData\TestDB_log.ldf'
, SIZE = 2048KB
, FILEGROWTH = 16384KB
);
GO USE TestDB
Go
IF OBJECT_ID('dbo.PrimaryTable_Large', 'U') IS NOT NULL
DROP TABLE dbo.PrimaryTable_Large
GO
CREATE TABLE PrimaryTable_Large
(
ID INT IDENTITY
PRIMARY KEY ,
SomeColumn CHAR(4) NULL ,
Filler CHAR(1500) DEFAULT ''
);
GO ALTER DATABASE TestDB SET RECOVERY FULL; /*Initial full database backup of TestDB*/
BACKUP DATABASE TestDB
TO DISK ='D:\SQLBackups\TestDB.bak'
WITH INIT;
GO DBCC SQLPERF(LOGSPACE) ;
/*Log Size (MB): 2, Log Space used (%): 18*/ --检查恢复模式
SELECT NAME,recovery_model_desc FROM sys.databases WHERE NAME='TestDB'
--last_log_backup_lsn为空则处于自动截断模式
SELECT DB_NAME(database_id) AS DatabaseName,last_log_backup_lsn
FROM master.sys.database_recovery_status WHERE database_id=DB_ID('TestDB')
查看数据库中VLFs信息
--Listing 2.2: Four VLFs in the newly created TestDB database.
-- how many VLFs?
USE TestDB;
GO
DBCC Loginfo
GO
结果返回4行意味着有4个VLFs。
FileSize列(单位Bytes)显示前3个VLFs小于0.5MB，第4个大于0.5MB，总大小为日志文件初始大小(2MB)。
Status=2表示活动的VLF，它不能被重写；Status=0表示非活动的VLF，它的空间可以重用。当前我们有一个活动的VLF。
FSeqNo列表示VLFs在日志文件中的逻辑顺序。
CreateLSN列是自动增长事件或者ALTER DATABASE操作(增长日志并创建VLF)的LSN。0值表示此VLFs是创建数据库时所创建的。
现在让我们往表中插入1500行记录，然后重新检查统计值
--Listing 2.3: VLFs after adding 1,500 rows.
USE TestDB;
GO
INSERT INTO PrimaryTable_Large
( SomeColumn, Filler
)
SELECT TOP 1500
'abcd ',
REPLICATE('a', 200)
FROM msdb.sys.columns a
CROSS JOIN msdb.sys.columns b
GO DBCC SQLPERF(LOGSPACE) ;
/*Log Size: 18 MB ; Log Space Used: 16%*/ DBCC Loginfo
GO
在插入1500行后，触发了日志文件自动增长。我们可以看到4个新的VLFs。增长量是16MB，每一个VLF是4MB。新VLFs的第一个(FSeqNo 43)是活动的，其他的未使用，
当前日志文件是18MB，使用率为16%。
现在执行一个日志备份，并重新检查统计值
--Listing 2.4: VLF usage after log backup.
/*now a log backup*/
BACKUP Log TestDB
TO DISK ='D:\SQLBackups\TestDB_log.bak'
WITH INIT;
GO DBCC SQLPERF(LOGSPACE) ;
/*Log Size: 18 MB ; Log Space Used: 6%*/ -- how many VLFs?
USE TestDB;
GO
DBCC Loginfo
GO
日志备份后，空间使用率降到6%，并且最原始的4个VLFs被截断(可重用)。让我们再次增长日志，增加15000条记录
BEGIN TRAN
INSERT INTO PrimaryTable_Large
( SomeColumn, Filler
)
SELECT TOP 15000
'abcd ',
REPLICATE('a', 200)
FROM msdb.sys.columns a
CROSS JOIN msdb.sys.columns b
--COMMIT TRAN
注意这里我开启了一个事务，暂时不提交。VLFs信息
SQL Server填充了VLFs 43-46(第一次自动增长创建)。最原始的4个VLFs(39-42，创建数据库时所创建)可以被重用，但是SQL Server选择了自动增长，而不是重用前面4个VLFs，然后填充新VLFs中的前3个(47-49)。
在本例中，SQL Server选择自动增长，是因为如果使用最原始的4个VLFs，将没有足够的log reservation空间用于回滚事务。回滚事务需要一系列的操作来undo原事务的影响。这些compensating operations会产生日志记录，类似于其他数据库变更。
Log reservation is the amount of log space(per transaction) that SQL Server must keep free in order to accommodate these compensation log records.
因此，大的事务写入很多行将需要非常大的log reservation，在事务提交后SQL Server会立刻释放这些额外的空间。
在另一个会话查看事务的log reservation
USE TestDB
GO
SELECT DTST.[session_id],
--DES.[login_name] AS [Login Name],
DB_NAME(DTDT.database_id) AS [Database],
DTDT.[database_transaction_begin_time] AS [Begin Time],
DATEDIFF(ms, DTDT.[database_transaction_begin_time], GETDATE()) AS [Duration ms],
CASE DTAT.transaction_type
WHEN 1 THEN 'Read/Write'
WHEN 2 THEN 'Read-Only'
WHEN 3 THEN 'System'
WHEN 4 THEN 'Distributed'
END AS [Transaction Type],
CASE DTAT.transaction_state
WHEN 0 THEN 'Not fully initialized'
WHEN 1 THEN 'Initialized, not started'
WHEN 2 THEN 'Active'
WHEN 3 THEN 'Ended'
WHEN 4 THEN 'Commit initiated'
WHEN 5 THEN 'Prepared, awaiting resolution'
WHEN 6 THEN 'Committed'
WHEN 7 THEN 'Rolling back'
WHEN 8 THEN 'Rolled back'
END AS [Transaction State],
DTDT.[database_transaction_log_record_count] AS [Log Records],
DTDT.[database_transaction_log_bytes_used] AS [Log Bytes Used],
DTDT.[database_transaction_log_bytes_reserved] AS [Log Bytes Reserved],
DEST.[text] AS [Last Transaction Text],
DEQP.[query_plan] AS [Last Query Plan]
FROM sys.dm_tran_database_transactions DTDT
INNER JOIN sys.dm_tran_session_transactions DTST
ON DTST.[transaction_id] = DTDT.[transaction_id]
INNER JOIN sys.[dm_tran_active_transactions] DTAT
ON DTST.[transaction_id] = DTAT.[transaction_id]
INNER JOIN sys.[dm_exec_sessions] DES
ON DES.[session_id] = DTST.[session_id]
INNER JOIN sys.dm_exec_connections DEC
ON DEC.[session_id] = DTST.[session_id]
LEFT JOIN sys.dm_exec_requests DER
ON DER.[session_id] = DTST.[session_id]
CROSS APPLY sys.dm_exec_sql_text (DEC.[most_recent_sql_handle]) AS DEST
OUTER APPLY sys.dm_exec_query_plan (DER.[plan_handle]) AS DEQP
WHERE DB_NAME(DTDT.database_id) = 'TestDB'
ORDER BY
DTDT.[database_transaction_log_bytes_used] DESC;
--ORDER BY [Duration ms] DESC; --简单点使用这个
SELECT d.name ,
--session_id ,
d.recovery_model_desc ,
--database_transaction_begin_time ,
database_transaction_log_record_count ,
database_transaction_log_bytes_used ,
database_transaction_log_bytes_reserved ,
DATEDIFF(ms, database_transaction_begin_time, GETDATE())
AS [Duration ms]
FROM sys.dm_tran_database_transactions AS dt
INNER JOIN sys.dm_tran_session_transactions AS st
ON dt.transaction_id = st.transaction_id
INNER JOIN sys.databases AS d ON dt.database_id = d.database_id
WHERE d.name = 'TestDB'
我们可以看到SQL Server Reserved约为5.3MB，而原始的4个VLFs总大小为2MB，不足以存储必要的compensation operations。因此SQL Server优先选择了自动增长。
下面内容引自《SQL Server Transaction Log Management》->Chapter 8: Optimizing Log Throughput->Page 178
The logging subsystem reserves space when logging a transaction to ensure that the log can't run out of space during a rollback. As such, the required log space is actually greater than the total size of log records for the operation.
In short, a rollback operation logs compensation log records, and if a rollback were to run out of log space, SQL Server would have to be mark the database as suspect. This log reservation is not actually "used" log space, it's just a specific amount of space that must remain free, but it can trigger auto-growth events if the log fills to a point where the "used space + reserved space = log size," and it is counted as used space for the purposes of DBCC SQLPERF(LOGSPACE).
因此，在事务提交后你可能会发现DBCC SQLPERF(LOGSPACE)返回空间使用率降低，即使数据库处于完整模式并且没有进行日志备份
在事务提交前/*Log Size: 34 MB ; Log Space Used: 96%*/
在事务提交后/*Log Size: 34 MB ; Log Space Used: 79%*/
中间的差值为34*(96-79)/100.0=5.78 MB，与事务Reserved的大小相当。
In the absence of any log reservation calculation, SQL Server would simply have filled the available VLFs and then grown the log.
因此，事务日志的增长模式很大程度上取决于用户的活动模式和事务的大小。如果我们将原来一次插入15000行拆分成三次小的插入(比如1500行、5000行、3500行)，我们将会看到类似下面的VLFs信息
注意SQL Server填充了VLFs 43-46(第一次自动增长创建)，然后环绕到原始VLFs的前两个(现在重用47和48)。这是因为原始的VLFs能够容纳小事务插入所需的log reservation。然后SQL Server再次自动增长，并且使用新的VLFs(49)而不是原始剩余的VLFs。
上面的例子只是插入相对较少的行，大家可以测试插入百万甚至更多的行，然后查看日志使用以及VLFs。当然这又涉及log fragmentation问题，大家可以查阅相关的文章。
17:25 2017/5/12 当有多个日志文件时，自动增长选取哪个文件增长？
《Microsoft SQL Server企业级平台管理实践》-->第1章、数据库空间管理-->1.4、文件自动增长和自动收缩
一直深信这个解释，现在测试下
/********** SQL Server Transaction Log Management->Chapter2 **********/
--Listing 2.1: Re-creating the TestDB database, plus PrimaryTable_Large
USE master ; IF DB_ID('TestDB') IS NOT NULL
BEGIN
ALTER DATABASE [TestDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
USE master ;
DROP DATABASE TestDB ;
END CREATE DATABASE TestDB ON PRIMARY
( NAME = N'TestDB'
, FILENAME = N'D:\SQLData\TestDB.mdf'
, SIZE = 199680KB
, FILEGROWTH = 16384KB
)
LOG ON --添加两个日志文件
( NAME = N'TestDB_log'
, FILENAME = N'D:\SQLData\TestDB_log.ldf'
, SIZE = 2048KB
, FILEGROWTH = 16384KB
)
,( NAME = N'TestDB_log1'
, FILENAME = N'D:\SQLData\TestDB_log1.ldf'
, SIZE = 2048KB
, FILEGROWTH = 16384KB
);
GO USE TestDB
Go
IF OBJECT_ID('dbo.PrimaryTable_Large', 'U') IS NOT NULL
DROP TABLE dbo.PrimaryTable_Large
GO
CREATE TABLE PrimaryTable_Large
(
ID INT IDENTITY PRIMARY KEY ,
SomeColumn CHAR(4) NULL ,
Filler CHAR(1500) DEFAULT ''
);
GO ALTER DATABASE TestDB SET RECOVERY FULL; /*Initial full database backup of TestDB*/
BACKUP DATABASE TestDB
TO DISK ='D:\SQLBackups\TestDB.bak'
WITH INIT;
GO --检查恢复模式
SELECT NAME,recovery_model_desc FROM sys.databases WHERE NAME='TestDB'
--last_log_backup_lsn为空则处于自动截断模式
SELECT DB_NAME(database_id) AS DatabaseName,last_log_backup_lsn
FROM master.sys.database_recovery_status WHERE database_id=DB_ID('TestDB') --Listing 2.3: VLFs after adding 20000 rows.
USE TestDB;
GO
--BEGIN TRAN
INSERT INTO PrimaryTable_Large
( SomeColumn, Filler
)
SELECT TOP 20000
'abcd ',
REPLICATE('a', 200)
FROM msdb.sys.columns a
CROSS JOIN msdb.sys.columns b
--COMMIT TRAN IF OBJECT_ID('tempdb..#Loginfo') is not null
DROP TABLE #Loginfo
CREATE TABLE #Loginfo(FileId INT,FileSize BIGINT,StartOffset BIGINT,FSeqNo INT,Status TINYINT,Parity INT,CreateLSN BIGINT)
INSERT INTO #Loginfo
EXEC('DBCC Loginfo')
SELECT * FROM #Loginfo ORDER BY Status desc,FSeqNo
数据库包含两个日志文件(初始大小2MB，增长量16MB)，恢复模式为完整，插入适量数据后的VLFs情况
由于是完整模式，且有对其进行完整备份，测试过程中没有对其做事务日志备份，随着数据插入，日志记录会写满日志文件然后触发自动增长。依据Status=2的FSeqNo列可以知道FileId按2->3->2->3交替进行自增长。如果有三个日志文件呢？测试发现它按照"日志文件1->日志文件2->日志文件3->日志文件1->日志文件2->日志文件3..."。所谓的自动增长当前的日志文件，以保证日志记录的连续，好像并不适用！！！