SQL Server Full Recovery Model – Impact of checkpoints

Hi Friends,

We all knew that, SQL Server Full Recovery Model log truncation occurs due to transaction log backup while under Simple recovery model log truncation occurs due to checkpoints. But here I want to show you the impact of checkpoint on your database while database is in SQL Server Full recovery model. Actually the concept is when you create any new database then by default the recovery model of that database will be full until you made any change on default setting. Here this database will be treated as under simple recovery model until first full backup will not be performed for this database. That means full recovery mode will not be effective for log truncation until you take first full backup. Let me explain you this thing practically:

--create the database
use master
go
create database TESTDB
go  
--Recovery model of this database should be FULL(By Default FULL)
select recovery_model_desc from sys.databases where name='TESTDB'
go
--create a table and insert some data to generate log records
use TESTDB
go
create table xttest 
(
    id int identity(1,1),
    name varchar(20)
)
go
begin tran
declare @temp int
set @temp=1
while @temp<10000
begin
insert into xttest values('SQLServerGeeks-Hyd')
set @temp=@temp+1
end
commit tran
go

Now we have one table named as xttest under TESTDB database where recovery model of database is full and inserted 9999 records in that table. Now let me show you the log usage of this database before checkpoint and as well as after the checkpoint occurs.

--now you can check the uses of log file
use testdb
go
DBCC SQLPERF('LOGSPACE')
go
--Now run checkpoint to truncate the log file
checkpoint
go
--check that log file has been truncated by checkpoint or not
DBCC SQLPERF('LOGSPACE')
Go

Here log space used before checkpoint = 52.63283

Here log space used after checkpoint = 16.47296

These values may be different on your systems. Difference between both above values clearly shows that log truncation took place under full recovery model. If we will take first full back on database then log truncation will not be occur due to checkpoint. let me show you this also practically :

use master
go
backup database TESTDB to disk='C:\TestDB_Native_Full.BAK'
go
use TESTDB
go
begin tran
declare @temp int
set @temp=1
while @temp<10000
begin
insert into xttest values('SQLServerGeeks-Ggn')
set @temp=@temp+1
end
commit tran
go
--now you can check the uses of log file
use testdb
go
DBCC SQLPERF('LOGSPACE')
go
--Now run checkpoint to truncate the log file
checkpoint
go
--check that log file has been truncated by checkpoint or not
DBCC SQLPERF('LOGSPACE')
go

Here log space used before checkpoint = 54.17457

Here log space used after checkpoint = 54.37619

This clearly shows that now log truncation not occurs due to checkpoint. Now truncation will only happen after taking transaction log backup:

Use testdb
go
begin tran
declare @temp int
set @temp=1
while @temp<10000
begin  
insert into xttest values('SQLServerGeeks-chn')
set @temp=@temp+1
end
commit tran
go
--now you can check the uses of log file
use testdb
go
DBCC SQLPERF('LOGSPACE')
go
--Now take tlog backup
Backup log TESTDB to disk='C:\TestDB_Native_log.trn'
go
--check that log file has been truncated by tlog backup
DBCC SQLPERF('LOGSPACE')
go

Here log space used before Transaction log backup = 65.78065

Here log space used after Transaction log backup = 17.26533

Which clearly shows that log backup will take care of log truncation after first full backup for a database under full recovery model.

HAPPY LEARNING!

Regards

Prince Rastogi

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter |  Follow me on FaceBook