I am looking for a way to take Checkpoints of a SQL Anywhere database every 2 hours (or so). Are there any tools that allow this to be done? I see a CHECKPOINT command available, but it seems that will need to be triggered via some event which I would like to avoid doing.

Thanks Suren

asked 13 Feb, 15:50

ssethuma's gravatar image

ssethuma
263
accept rate: 0%

So what are you trying to accomplish?

(14 Feb, 03:21) Volker Barth

By default, checkpoints are performed automatically at least every 60 minutes but typically more often: every 20 minutes on an idle server IIRC. The actual frequency is governed by heuristics based on the CHECKPOINT_TIME & RECOVERY_TIME options as well as the state of the cache and other things going on in the server. CHECKPOINT_TIME sets the maximum amount of time between checkpoints (unless prevented by things such as long-running backups, certain ALTER statements, etc). See

How the database server decides when to checkpoint

checkpoint_time option

recovery_time option

permanent link

answered 13 Feb, 16:26

John%20Smirnios's gravatar image

John Smirnios
10.3k387135
accept rate: 37%

edited 14 Feb, 07:40

In addition to John's answer, there are several statements that trigger checkpoints automatically, for a list see here.

Among others this contains the BACKUP DATABASE statement, which allows to influence when checkpoints are done. The same applies to the DBBACKUP tool with its options. - Basically you won't need to call an explicit checkpoint while doing a backup but you might consider which backup option to choose.

permanent link

answered 14 Feb, 03:28

Volker%20Barth's gravatar image

Volker Barth
36.1k342501749
accept rate: 34%

edited 14 Feb, 03:29

Thanks John and Volker for the quick responses. I will look into the dbbackup tool options. But basically, I was trying to avoid taking a full backup. I was hoping to take a snapshot of the database (like Sybase does using VSS on Windows). I just need snapshot for quick recovery but perhaps restores from backups are as quick.

Thanks Suren

permanent link

answered 14 Feb, 08:03

ssethuma's gravatar image

ssethuma
263
accept rate: 0%

1

To be clear: A checkpoint is not an alternative to a backup, and it is not a "snapshot" of anything. It is a process that brings the physical database file into a "state of grace" where it agrees with what's in the RAM cache. This state of grace may be momentary if updates are continuous.

In the case of a backup, a checkpoint is taken just before the database file is copied, ensuring that the backup file is in a "state of grace"... if updates continue, the subsequent backup log file is newer than the backup database file, so the backup log is required in order to start the backup database file; i.e., the "state of grace" is out of date.

A checkpoint is also taken just before the database is shut down, ensuring that the database file is in a frozen state of grace... which implies the transaction log file isn't needed for recovery on startup.

(14 Feb, 08:43) Breck Carter
1

SQLAnywhere supports VSS on Windows. See SQL Anywhere Volume Shadow Copy Service (VSS)

Alll the dbvss service effectively does is perform a checkpoint just prior to the snapshot being taken. It also tells Windows all of the volumes (disks) in used by the server (dbspaces, log file, etc) so that all of the volumes are snapshotted together.

Even without dbvss, you can still use VSS under Windows except it is up to the user to ensure all relevant volumes are snapshotted at the same time & you need to be aware that the snapshot of the database will need to undergo crash recovery if you use it for you restoration plan. If you run the dbvss service, the database is, as mentioned above, checkpoint and therefore is in a clean state that doesn't require crash recovery.

(14 Feb, 09:13) John Smirnios

Thank you, Breck and John.

(14 Feb, 09:23) ssethuma
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×10

question asked: 13 Feb, 15:50

question was seen: 160 times

last updated: 14 Feb, 09:23