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

trace_flag_3505_1

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.

trace_flag_3505_2

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

trace_flag_3505_3

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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

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 →

Leave a Reply

Your email address will not be published. Required fields are marked *