I tried to setup excercise in full recovery model that shows in log truncation that occured no VLF's are marked inactive because the lack of the CHECKPOINT.
But, it turns out that checkpoint indeed occurs even at the beginning of a log backup,
not just full/diff backup. At least in SQL2012 instance i tried that.
Here it is, try it:
-- We will prevent file growth by setting maxsize equal to initial size
create database TestCKPT ON
PRIMARY
(NAME = data,
FILENAME = 'D:\temp\TestCKPT.mdf',
SIZE = 100 MB,
MAXSIZE = 100 MB
)
LOG ON
(NAME = log,
FILENAME = 'D:\temp\TestCKPT.ldf',
SIZE = 65544 KB, -- 64MB + 1 page
MAXSIZE = 65544 KB
)
GO
-- We wont to control when checkpoint occur. So, we will disable automatic checkpoins by trace flag 3505
-- to prevent automatic checkpoint occur for duration of the test.
-- Alternative would be increasing the RECOVERY INTERVAL parameter.
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'recovery interval', 32767
GO
RECONFIGURE WITH OVERRIDE
GO
ALTER DATABASE TestCKPT SET TARGET_RECOVERY_TIME = 3600 MINUTES
GO
-- Pu full recovery model in effect
USE TestCKPT
alter database TestCKPT SET recovery FULL
-- Until we take a full backup, we are effectively in SIMPLE recovery model,
-- even if sys.databases shows it is in FULL.
-- So let's take a full backup to kick-start full rm:
BACKUP DATABASE TestCKPT TO DISK = 'D:\temp\TestCKPT.bak'
backup log TestCKPT TO DISK = 'TestCKPT.ldf'
GO
-- Checkpoint location is in VLF that is first part of three-part LSN.
-- That is 21 in our case:
SELECT
DB_NAME() AS DatabaseName
, [Current LSN]
, [Previous LSN]
, Operation
, [Checkpoint Begin]
, [Checkpoint End]
, [Dirty Pages]
FROM fn_dblog(NULL, NULL)
WHERE operation IN ( 'LOP_BEGIN_CKPT', 'LOP_END_CKPT')
-- This is a list of all VLF's (one row equals one VLF).
-- We can see that active VLF is the one with VLF seqence number 21 (the one that checkpoint is)
declare @vlfs table
(RecoveryUnitId int, -- sql2012 only
FileId int,
FileSize bigint, -- VLF size in bytes
StartOffset bigint, -- VLF offset in bytes from beginning of transaction log
FSeqNo int,
Status int,
Parity tinyint,
CreateLSN decimal(25,0)
)
insert into @vlfs
exec('DBCC LOGINFO () WITH TABLERESULTS, NO_INFOMSGS')
SELECT
VLF_SeqNo = convert(varbinary(4),v.FSeqNo),
file_id = v.FileId, logical_name = f.name, f.physical_name, log_size_kb = REPLACE(CONVERT(varchar, f.size*$8, 1), '.00', ''),
vlf_size_kb = REPLACE(CONVERT(varchar, v.FileSize/$1024, 1), '.00', ''),
vlf_physical_offset = v.StartOffset,
WriteSequenceNo = ROW_NUMBER() OVER(ORDER BY v.FSeqNo),
Status = CASE WHEN v.Status=2 THEN 'ACTIVE' ELSE 'INACTIVE (free)' END,
v.CreateLSN
FROM @vlfs v
JOIN sys.database_files f on f.file_id = v.FileId
order by v.StartOffset
GO
-- Let's make some transaction to move log insertion point to the next VLF
CREATE TABLE wide(x nchar(4000) DEFAULT 'A') -- a bit less than 8KB, one page
GO
INSERT INTO wide default values
GO 1024
-- By running previous queries, confirm that we have moved to the next VLF (22) - now we have two active VLFs
-- and confirm that no checkpoint has occured other than the one we saw in previous VLF (21).
-- We dont have any open transactions. Will log backup mark the first VLF inactive (clear it) ?
SELECT
DB_NAME() AS DatabaseName
, [Current LSN]
, [Previous LSN]
, Operation
, [Checkpoint Begin]
, [Checkpoint End]
, [Dirty Pages]
FROM fn_dblog(NULL, NULL)
WHERE operation IN ( 'LOP_BEGIN_CKPT', 'LOP_END_CKPT')
backup log TestCKPT TO DISK = 'TestCKPT2.ldf'
SELECT
DB_NAME() AS DatabaseName
, [Current LSN]
, [Previous LSN]
, Operation
, [Checkpoint Begin]
, [Checkpoint End]
, [Dirty Pages]
FROM fn_dblog(NULL, NULL)
WHERE operation IN ( 'LOP_BEGIN_CKPT', 'LOP_END_CKPT')
-- TURNS OUT CHECKPOINT OCCURED ON TRAN LOG BACKUP!
-- cleanup
use master
drop database TestCKPT
GO