Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [checkpoint]

checkpoint is a database event. when checkpoint happens, all dirty blocks(pages) in the memory are written to database data files.

47 questions
19
votes
1 answer

Checkpoints are occurring too frequently during pg_restore

Under PostgreSQL 9.2.2 (Windows 32 bits), I have a pg_restore command that systematically results in log warnings about the checkpoint frequency, for example: LOG: checkpoints are occurring too frequently (17 seconds apart) HINT: Consider…
Sébastien Clément
  • 1,825
  • 3
  • 19
  • 28
11
votes
1 answer

"truncate log on checkpoint" option in SQL Server

Long story but our long term consultants (former employees) wrote a custom script years back (2006 or so) to interface with Tivoli Storage Manager and it appears to be checking for a SQL Server DB option named truncate log on checkpoint. Their claim…
user3885315
  • 113
  • 1
  • 1
  • 6
10
votes
2 answers

Increased waits during checkpoint after upgrading to better storage

When we migrated from an older all flash array, to a newer all flash array (different, but well established vendor), we started seeing increased waits in SQL Sentry during checkpoints. Version: SQL Server 2012 Sp4 On our old storage our waits…
Doley
  • 111
  • 5
9
votes
1 answer

What Happens to dirty pages if the system fails before the next checkpoint?

Assuming a database using full recovery model, when a record is written in SQL Server (by INSERT / UPDATE etc) write ahead logging will ensure the change is written to the log file before modifying the data page. Both the log and data page entries…
SE1986
  • 2,142
  • 4
  • 30
  • 61
8
votes
2 answers

PostgreSQL checkpoint log explained

I know what PostgreSQL checkpoint is and when it is happening. I need some additional information about the logs produced by the log_checkpoints = on parameter, so please explain some points of it to me: 2017-09-09 16:31:37 EEST [6428-6524] LOG: …
inivanoff1
  • 183
  • 1
  • 1
  • 3
7
votes
1 answer

How can I solve postgresql problem after deleting wal files?

I turned the archive_mode on on my postgresql configuration for testing a backup server . And since the wal files took lots of disk space, after my test I turned it off and also deleted the wal files. When I tried to restart postgresql I got the…
Sina
  • 115
  • 1
  • 2
  • 7
6
votes
2 answers

MS SQL Server - Checkpoint process stuck in SLEEP_BPOOL_FLUSH

We seem to have a problem that came out of nowhere. The checkpoint process is stuck in SLEEP_BPOOL_FLUSH state, and not actually reducing the usage in the log file. This never seemed to happen before. I understand that SLEEP_BPOOL_FLUSH is a normal…
apt605
  • 83
  • 1
  • 1
  • 5
5
votes
1 answer

Dirty buffer pages after issuing CHECKPOINT

I am currently working on a test system and due to the nature of the queries I want to optimise, I am trying to simulate a "cold" read a well as I can. Part of that is clearing the buffer cache before performing the queries. From everything I can…
Florian
  • 341
  • 2
  • 9
5
votes
1 answer

Checkpoints on secondary replica AlwaysOn AG

Setup 3 Node Alwayson cluster - 1 sync and 1 async secondary replica - SQL Server 2012 Situation We are witnessing PageIOLatches when reading from the asynchronous secondary replica. This is mostly caused by the throughput of the SAN that has…
Thomas Costers
  • 738
  • 4
  • 11
5
votes
2 answers

Using CHECKPOINT vs GO

Typically, when one of the developers or data analysts needs to perform a very large update or deletion of data (where a truncate or truncate/insert wouldn't make sense because the data set to keep is too large) I recommend to them to do something…
Alf47
  • 981
  • 1
  • 9
  • 22
3
votes
3 answers

What does "thread" mean in "thread checkpoints" context?

The following excerpt is from Oracle concepts. Maybe this is a stupid question, but I really need your help to understand it. My question is: What's does the thread mean in the context thread checkpoints? Does it means redo log thread(all redo log…
Just a learner
  • 2,082
  • 7
  • 36
  • 57
3
votes
1 answer

Checkpoints and Logging

I'm working on getting full grasp of logging architecture. Please correct me if I'm wrong here: I understand that a checkpoint occurs (also lazywriter) to write the dirty pages in the memory to the disk. Disk here means transaction log file no…
Stackoverflowuser
  • 1,550
  • 3
  • 27
  • 42
3
votes
2 answers

In SQL Server, what's the impact of a checkpoint to log records in log buffer?

Does checkpoint operations flush everything in the log buffer to log file? Or just the log records relating to the dirty pages that are about to be flushed? I found some inconsistent description about this by the top names in the SQL Server…
Just a learner
  • 2,082
  • 7
  • 36
  • 57
3
votes
1 answer

High number of Checkpoint pages/sec and memory pressure

Recently I read a blog post on mssqltips.com about memory bottlenecks on SQL Server. In this article I read following: The following performance counters on SQL Server: Buffer Manager object can also indicate memory pressure: High number of…
Frederik Vanderhaegen
  • 2,122
  • 1
  • 17
  • 36
3
votes
1 answer

What happens when checkpoint_segments reached?

I'm a bit confused with checkpoint_segments definition in PostgreSQL documentation. What happens when checkpoint_segments reached? The dirty pages in shared_buffers written to WAL. Then what is the usage of background writer? WAL is merged to…
Majid Azimi
  • 2,351
  • 3
  • 23
  • 24
1
2 3 4