sql server trace flag 3505

Hi Friends,

This is my 8th blog on SQL Server Trace Flag 3505 in the series of One Trace Flag a Day. You can go to all blogs under that series by clicking here.

Trace Flag 3505 is related to SQL Server Checkpoint. I hope we all are aware of checkpoint in SQL Server. Those who are not aware of it click here.

1. From the above link we got the information that there are many types of checkpoints in SQL Server. Automatic checkpoint is one of them and by default, time interval between automatic checkpoints managed by SQL Server internally.
2. From previous blog in this series we have seen that checkpoint perform some IO operation within database (to write dirty pages from buffer to disk).

Now think about a scenario like system is already under heavy IO load and checkpoint can also add more IO load on the server. Here we can enable trace flag 3505 to disable automatic checkpoints across the server i.e. for all databases on the instance. So we can say that by using trace flag 3505, we can control the behavior of automatic checkpoints.

Let me show you this practically. First I will show you the occurrence of automatic checkpoints.

Use master
go
CREATE DATABASE [CheckpointTest3]
 ON  PRIMARY 
( NAME = N'CheckpointTest3', FILENAME = N'F:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014A\MSSQL\DATA\CheckpointTest3.mdf' , SIZE = 124MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'CheckpointTest3_log', FILENAME = N'F:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014A\MSSQL\DATA\CheckpointTest3_log.ldf' , SIZE = 64MB , MAXSIZE = 2048GB , FILEGROWTH = 64MB)
GO
--Enabling below both trace flags will logged the checkpoint operation in SQL Error Log
DBCC TRACEON(3502,3504,-1)
go
alter database CheckpointTest3 set recovery SIMPLE;
go
use CheckpointTest3
go
create table checktable
(a int, b int)
go
exec sp_cycle_errorlog
go
insert into checktable values(101,232)
go 10000
use master
go
DBCC TRACEOFF(3502,3504,-1)
go

From the above code, I will show you the error log entries for automatic checkpoints due to trace flag 3502 and 3504. To see the error log entries run the below command or you can check error log from SSMS.

exec xp_readerrorlog

Now I will show you the use of trace flag 3505 that will disable the automatic checkpoints.

Use master
go
CREATE DATABASE [CheckpointTest3]
 ON  PRIMARY 
( NAME = N'CheckpointTest3', FILENAME = N'F:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014A\MSSQL\DATA\CheckpointTest3.mdf' , SIZE = 124MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'CheckpointTest3_log', FILENAME = N'F:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014A\MSSQL\DATA\CheckpointTest3_log.ldf' , SIZE = 64MB , MAXSIZE = 2048GB , FILEGROWTH = 64MB)
GO
DBCC TRACEON(3502,3504,3505,-1)
go
alter database CheckpointTest3 set recovery SIMPLE;
go
use CheckpointTest3
go
create table checktable
(a int, b int)
go
exec sp_cycle_errorlog
go
insert into checktable values(101,232)
go 10000

From the above code, I will show you the error log entries for automatic checkpoints due to trace flag 3505. To see the error log entries run the below command you can check error log from SSMS.

Now from above log it is clear that after error log recycle there is no checkpoint entry because automatic checkpoint is disabled due to trace flag 3505. Now you can execute manual checkpoint here that will logged in error log also.

go
checkpoint
go
use master
go
DBCC TRACEOFF(3502,3504,3505,-1)
go
exec xp_readerrorlog
go

This is not as much simple or easy as you are thinking right now because if automatic checkpoint will be disabled then you will fire checkpoints manually and this may also increase recovery time. More recovery time means more down time.

PS: Do not use trace flags in production environment without testing it on non production environments and without consulting because everything comes at a cost.

HAPPY LEARNING!

Regards:
Prince Kumar Rastogi

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

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook