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.

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.

trace_flag_3505_1

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

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.

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