|
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 |
|
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 |
|
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. |
|
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 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 '20, 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 '20, 09:13)
John Smirnios
Thank you, Breck and John.
(14 Feb '20, 09:23)
ssethuma
|
So what are you trying to accomplish?