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

1_SQL_Server_Impact_of_checkpoint_under_full_recovery_mode

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

2_SQL_Server_Impact_of_checkpoint_under_full_recovery_mode

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

3_SQL_Server_Impact_of_checkpoint_under_full_recovery_mode

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 TwitterFollow me on FaceBook

   

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

3 Comments on “SQL Server Full Recovery Model – Impact of checkpoints”

  1. Finally! I found what i’ve been looking for days… Thank you for this great post.. you helped me understand something that was bothering me for a really long time!… Thanks again.

  2. Great explanation. Thanks a ton!!!
    Had a little doubt, as stated “If we will take first full back on database then log truncation will not be occur due to checkpoint.” and truncation will only happen after taking transaction log backup. So am getting confused if in simple recovery model if I have full backup taken on database then also log truncation will not happen due to checkpoint ?

Leave a Reply

Your email address will not be published.