January 13, 2013 at 7:22 am
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
January 13, 2013 at 9:38 am
Vedran Kesegic (1/13/2013)
But, it turns out that checkpoint indeed occurs even at the beginning of a log backup, not just full/diff backup.
It can occur. It's not an automatic thing that always happens, but it can (I've seen checkpoints triggered by a log backup when either traceflags or server settings have resulted in a lack of automatic checkpoints).
But the checkpoint (better to say: the lack of it) can prevent VLF to be cleared (when clearing process is triggered by log backup).
Of course it can.
What I'm saying, and have been saying, is that a checkpoint after a log backup will not mark additional log records as reusable and hence will not allow a shrink to reclaim more space than it would if the checkpoint had not been run after the log backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 13, 2013 at 11:00 am
a checkpoint after a log backup will not mark additional log records as reusable and hence will not allow a shrink to reclaim more space than it would if the checkpoint had not been run after the log backup.
Totally agree.
So, the order would be:
1) checkpoint
2) BACKUP LOG MYDatabase TO DISK='D:\transactionLogBackup1.trn'
3) DBCC SHRINKFILE('MyTransactionLogName', 1000) -- 1000 MB. Set as you like.
Repeat 1-3 until shrinked
January 13, 2013 at 12:54 pm
That would work better than the order you originally had, yes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply