SYSTEM Statements

Note

In case of [`prefer_localhost_replica`](/docs/operations/settings/settings#prefer_localhost_replica) is enabled (the default), the data to local shard will be inserted anyway.

### SYSTEM FLUSH DISTRIBUTED[​](#flush-distributed "Direct link to SYSTEM FLUSH DISTRIBUTED")

Forces ClickHouse to send data to cluster nodes synchronously. If any nodes are unavailable, ClickHouse throws an exception and stops query execution. You can retry the query until it succeeds, which will happen when all nodes are back online.

You can also override some settings via `SETTINGS` clause, this can be useful to avoid some temporary limitations, like `max_concurrent_queries_for_all_users` or `max_memory_usage`.

```
SYSTEM FLUSH DISTRIBUTED [db.]<distributed_table_name> [ON CLUSTER cluster_name] [SETTINGS ...]
```

Note

Each pending block is stored in disk with settings from the initial INSERT query, so that is why sometimes you may want to override settings.

### SYSTEM START DISTRIBUTED SENDS[​](#start-distributed-sends "Direct link to SYSTEM START DISTRIBUTED SENDS")

Enables background data distribution when inserting data into distributed tables.

```
SYSTEM START DISTRIBUTED SENDS [db.]<distributed_table_name> [ON CLUSTER cluster_name]
```

### SYSTEM STOP LISTEN[​](#stop-listen "Direct link to SYSTEM STOP LISTEN")

Closes the socket and gracefully terminates the existing connections to the server on the specified port with the specified protocol.

However, if the corresponding protocol settings were not specified in the clickhouse-server configuration, this command will have no effect.

```
SYSTEM STOP LISTEN [ON CLUSTER cluster_name] [QUERIES ALL | QUERIES DEFAULT | QUERIES CUSTOM | TCP | TCP WITH PROXY | TCP SECURE | HTTP | HTTPS | MYSQL | GRPC | POSTGRESQL | PROMETHEUS | CUSTOM 'protocol']
```

- If `CUSTOM 'protocol'` modifier is specified, the custom protocol with the specified name defined in the protocols section of the server configuration will be stopped.
- If `QUERIES ALL [EXCEPT .. [,..]]` modifier is specified, all protocols are stopped, unless specified with `EXCEPT` clause.
- If `QUERIES DEFAULT [EXCEPT .. [,..]]` modifier is specified, all default protocols are stopped, unless specified with `EXCEPT` clause.
- If `QUERIES CUSTOM [EXCEPT .. [,..]]` modifier is specified, all custom protocols are stopped, unless specified with `EXCEPT` clause.

### SYSTEM START LISTEN[​](#start-listen "Direct link to SYSTEM START LISTEN")

Allows new connections to be established on the specified protocols.

However, if the server on the specified port and protocol was not stopped using the SYSTEM STOP LISTEN command, this command will have no effect.

```
SYSTEM START LISTEN [ON CLUSTER cluster_name] [QUERIES ALL | QUERIES DEFAULT | QUERIES CUSTOM | TCP | TCP WITH PROXY | TCP SECURE | HTTP | HTTPS | MYSQL | GRPC | POSTGRESQL | PROMETHEUS | CUSTOM 'protocol']
```

## Managing MergeTree Tables[​](#managing-mergetree-tables "Direct link to Managing MergeTree Tables")

ClickHouse can manage background processes in [MergeTree](/docs/engines/table-engines/mergetree-family/mergetree) tables.

### SYSTEM STOP MERGES[​](#stop-merges "Direct link to SYSTEM STOP MERGES")

Not supported in ClickHouse Cloud

Provides possibility to stop background merges for tables in the MergeTree family:

```
SYSTEM STOP MERGES [ON CLUSTER cluster_name] [ON VOLUME <volume_name> | [db.]merge_tree_family_table_name]
```

Note

`DETACH / ATTACH` table will start background merges for the table even in case when merges have been stopped for all MergeTree tables before.

### SYSTEM START MERGES[​](#start-merges "Direct link to SYSTEM START MERGES")

Not supported in ClickHouse Cloud

Provides possibility to start background merges for tables in the MergeTree family:

```
SYSTEM START MERGES [ON CLUSTER cluster_name] [ON VOLUME <volume_name> | [db.]merge_tree_family_table_name]
```

### SYSTEM STOP TTL MERGES[​](#stop-ttl-merges "Direct link to SYSTEM STOP TTL MERGES")

Provides possibility to stop background delete old data according to [TTL expression](/docs/engines/table-engines/mergetree-family/mergetree#table_engine-mergetree-ttl) for tables in the MergeTree family:
Returns `Ok.` even if table does not exist or table has not MergeTree engine. Returns error when database does not exist:

```
SYSTEM STOP TTL MERGES [ON CLUSTER cluster_name] [[db.]merge_tree_family_table_name]
```

### SYSTEM START TTL MERGES[​](#start-ttl-merges "Direct link to SYSTEM START TTL MERGES")

Provides possibility to start background delete old data according to [TTL expression](/docs/engines/table-engines/mergetree-family/mergetree#table_engine-mergetree-ttl) for tables in the MergeTree family:
Returns `Ok.` even if table does not exist. Returns error when database does not exist:

```
SYSTEM START TTL MERGES [ON CLUSTER cluster_name] [[db.]merge_tree_family_table_name]
```

### SYSTEM STOP MOVES[​](#stop-moves "Direct link to SYSTEM STOP MOVES")

Provides possibility to stop background move data according to [TTL table expression with TO VOLUME or TO DISK clause](/docs/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl) for tables in the MergeTree family:
Returns `Ok.` even if table does not exist. Returns error when database does not exist:

```
SYSTEM STOP MOVES [ON CLUSTER cluster_name] [[db.]merge_tree_family_table_name]
```

### SYSTEM START MOVES[​](#start-moves "Direct link to SYSTEM START MOVES")

Provides possibility to start background move data according to [TTL table expression with TO VOLUME and TO DISK clause](/docs/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl) for tables in the MergeTree family:
Returns `Ok.` even if table does not exist. Returns error when database does not exist:

```
SYSTEM START MOVES [ON CLUSTER cluster_name] [[db.]merge_tree_family_table_name]
```

### SYSTEM SYSTEM UNFREEZE[​](#query_language-system-unfreeze "Direct link to SYSTEM SYSTEM UNFREEZE")

Clears a frozen backup with the specified name from all the disks. See more about unfreezing separate parts in [ALTER TABLE table\_name UNFREEZE WITH NAME](/docs/sql-reference/statements/alter/partition#unfreeze-partition)

```
SYSTEM UNFREEZE WITH NAME <backup_name>
```

### SYSTEM WAIT LOADING PARTS[​](#wait-loading-parts "Direct link to SYSTEM WAIT LOADING PARTS")

Wait until all asynchronously loading data parts of a table (outdated data parts) will became loaded.

```
SYSTEM WAIT LOADING PARTS [ON CLUSTER cluster_name] [db.]merge_tree_family_table_name
```

## Managing ReplicatedMergeTree Tables[​](#managing-replicatedmergetree-tables "Direct link to Managing ReplicatedMergeTree Tables")

ClickHouse can manage background replication related processes in [ReplicatedMergeTree](/docs/engines/table-engines/mergetree-family/replication) tables.

### SYSTEM STOP FETCHES[​](#stop-fetches "Direct link to SYSTEM STOP FETCHES")

Not supported in ClickHouse Cloud

Provides possibility to stop background fetches for inserted parts for tables in the `ReplicatedMergeTree` family:
Always returns `Ok.` regardless of the table engine and even if table or database does not exist.

```
SYSTEM STOP FETCHES [ON CLUSTER cluster_name] [[db.]replicated_merge_tree_family_table_name]
```

### SYSTEM START FETCHES[​](#start-fetches "Direct link to SYSTEM START FETCHES")

Not supported in ClickHouse Cloud

Provides possibility to start background fetches for inserted parts for tables in the `ReplicatedMergeTree` family:
Always returns `Ok.` regardless of the table engine and even if table or database does not exist.

```
SYSTEM START FETCHES [ON CLUSTER cluster_name] [[db.]replicated_merge_tree_family_table_name]
```

### SYSTEM STOP REPLICATED SENDS[​](#stop-replicated-sends "Direct link to SYSTEM STOP REPLICATED SENDS")

Provides possibility to stop background sends to other replicas in cluster for new inserted parts for tables in the `ReplicatedMergeTree` family:

```
SYSTEM STOP REPLICATED SENDS [ON CLUSTER cluster_name] [[db.]replicated_merge_tree_family_table_name]
```

### SYSTEM START REPLICATED SENDS[​](#start-replicated-sends "Direct link to SYSTEM START REPLICATED SENDS")