On this page
Takes a snapshot of the given database and truncates the transaction log.
SNAPSHOT [DATABASE] <database_name>;
database_is the name of the database to snapshot.
A user must have either the
BACKUPprivilege to run this command.
SNAPSHOT DATABASEforces a manual snapshot, and can only be run on the master aggregator.
This command causes implicit commits.
See COMMIT for more information.
Refer to the Permission Matrix for the required permission.
This command forces data to the object store if you are using bottomless.
In the Premium edition, using the CREATE MILESTONE command with a meaningful milestone name makes it easy to attach the database at the point marked by the milestone.
During normal operation, SingleStore Helios logs writes and DDL commands to transaction log files.
When the transaction log reached the threshold set by
After large ingests to columnstore tables, during which new columnstore segments are written or merged during ingest, and old (immutable) segments need to be cleared out.
The snapshot effectively accomplishes this, since they are stored as long as the transaction log references them.
SNAPSHOT DATABASE command allows you to force a snapshot operation on a given database across all the partitions in a workspace.
SNAPSHOT DATABASE is primarily useful to compact the logs to enable faster recovery if the SingleStore Helios service is restarted.
SNAPSHOT on a database:
After changing the schema of a large table using
ALTER TABLEto avoid the cost of replaying the
ALTERoperation if a leaf were ever to restart.
In practice, this situation comes up most often during prototyping and development of your application when schemas are frequently changed.
Prior to performing maintenance operations (such as SingleStore Helios upgrades) that require you to take leaves offline, especially if your workload is highly transactional.
Consider a database where DDL queries being run frequently cancel each other out, such as
CREATE TABLE and
DROP TABLE for the same table.
Note: For workloads with ETL transactions, some users choose to take a snapshot at the end of each transaction in order to keep the workspace ready for a restart.
snapshot_ (sometimes more than once), or enough data is ingested to trigger a snapshot.
Although taking frequent snapshots is beneficial in many cases, users who utilize DR workspace replication should exercise caution when configuring
snapshot_ or triggering manual snapshots with
SNAPSHOT DATABASE database_name;
Query OK, 0 rows affected (45.36 sec)
Last modified: January 11, 2023