This page describes how to configure database flags for Cloud SQL, and lists the flags that you can set for your instance. You use database flags for many operations, including adjusting SQL Server parameters, adjusting options, and configuring and tuning an instance.
When you set, remove, or modify a flag for a database instance, the database might be restarted. The flag value is then persisted for the instance until you remove it. If the instance is the source of a replica, and the instance is restarted, the replica is also restarted to align with the current configuration of the instance.
Configure database flags
Set a database flag
Console
- In the Google Cloud console, select the project that contains the Cloud SQL instance for which you want to set a database flag.
- Open the instance and click Edit.
- Scroll down to the Flags section.
- To set a flag that has not been set on the instance before, click Add item, choose the flag from the drop-down menu, and set its value.
- Click Save to save your changes.
- Confirm your changes under Flags on the Overview page.
gcloud
Edit the instance:
gcloud sql instances patch INSTANCE_NAME --database-flags=FLAG1=VALUE1,FLAG2=VALUE2
This command will overwrite all database flags previously set. To keep those and add new ones, include the values for all flags you want set on the instance; any flag not specifically included is set to its default value. For flags that do not take a value, specify the flag name followed by an equals sign ("=").
For example, to set the 1204
,
remote access
, and remote query timeout (s)
flags, you
can use the following command:
gcloud sql instances patch INSTANCE_NAME \ --database-flags="1204"=on,"remote access"=on,"remote query timeout (s)"=300
Terraform
To add database flags, use a Terraform resource.
Apply the changes
To apply your Terraform configuration in a Google Cloud project, complete the steps in the following sections.
Prepare Cloud Shell
- Launch Cloud Shell.
-
Set the default Google Cloud project where you want to apply your Terraform configurations.
You only need to run this command once per project, and you can run it in any directory.
export GOOGLE_CLOUD_PROJECT=PROJECT_ID
Environment variables are overridden if you set explicit values in the Terraform configuration file.
Prepare the directory
Each Terraform configuration file must have its own directory (also called a root module).
-
In Cloud Shell, create a directory and a new
file within that directory. The filename must have the
.tf
extension—for examplemain.tf
. In this tutorial, the file is referred to asmain.tf
.mkdir DIRECTORY && cd DIRECTORY && touch main.tf
-
If you are following a tutorial, you can copy the sample code in each section or step.
Copy the sample code into the newly created
main.tf
.Optionally, copy the code from GitHub. This is recommended when the Terraform snippet is part of an end-to-end solution.
- Review and modify the sample parameters to apply to your environment.
- Save your changes.
-
Initialize Terraform. You only need to do this once per directory.
terraform init
Optionally, to use the latest Google provider version, include the
-upgrade
option:terraform init -upgrade
Apply the changes
-
Review the configuration and verify that the resources that Terraform is going to create or
update match your expectations:
terraform plan
Make corrections to the configuration as necessary.
-
Apply the Terraform configuration by running the following command and entering
yes
at the prompt:terraform apply
Wait until Terraform displays the "Apply complete!" message.
- Open your Google Cloud project to view the results. In the Google Cloud console, navigate to your resources in the UI to make sure that Terraform has created or updated them.
Delete the changes
To delete your changes, do the following:
- To disable deletion protection, in your Terraform configuration file set the
deletion_protection
argument tofalse
.deletion_protection = "false"
- Apply the updated Terraform configuration by running the following command and
entering
yes
at the prompt:terraform apply
-
Remove resources previously applied with your Terraform configuration by running the following command and entering
yes
at the prompt:terraform destroy
REST v1
To set a flag for an existing database:
Before using any of the request data, make the following replacements:
- project-id: The project ID
- instance-id: The instance ID
HTTP method and URL:
PATCH https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id
Request JSON body:
{ "settings": { "databaseFlags": [ { "name": "flag_name", "value": "flag_value" } ] } }
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
If there are existing flags configured for the database, modify the previous
command to include them. The PATCH
command overwrites the existing
flags with the ones specified in the request.
REST v1beta4
To set a flag for an existing database:
Before using any of the request data, make the following replacements:
- project-id: The project ID
- instance-id: The instance ID
HTTP method and URL:
PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id
Request JSON body:
{ "settings": { "databaseFlags": [ { "name": "flag_name", "value": "flag_value" } ] } }
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
If there are existing flags configured for the database, modify the previous
command to include them. The PATCH
command overwrites the existing
flags with the ones specified in the request.
Clear all flags to their default values
Console
- In the Google Cloud console, select the project that contains the Cloud SQL instance for which you want to clear all flags.
- Open the instance and click Edit.
- Open the Database flags section.
- Click the X next to all of the flags shown.
- Click Save to save your changes.
gcloud
Clear all flags to their default values on an instance:
gcloud sql instances patch INSTANCE_NAME \ --clear-database-flags
You are prompted to confirm that the instance will be restarted.
REST v1
To clear all flags for an existing instance:
Before using any of the request data, make the following replacements:
- project-id: The project ID
- instance-id: The instance ID
HTTP method and URL:
PATCH https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id
Request JSON body:
{ "settings": { "databaseFlags": [] } }
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
REST v1beta4
To clear all flags for an existing instance:
Before using any of the request data, make the following replacements:
- project-id: The project ID
- instance-id: The instance ID
HTTP method and URL:
PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id
Request JSON body:
{ "settings": { "databaseFlags": [] } }
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
Determine which database flags have been set for an instance
To see which flags have been set for a Cloud SQL instance:
Console
- In the Google Cloud console, select the project that contains the Cloud SQL instance for which you want to see the database flags that have been set.
- Select the instance to open its Instance Overview page.
The database flags that have been set are listed under the Database flags section.
gcloud
Get the instance state:
gcloud sql instances describe INSTANCE_NAME
In the output, database flags are listed under the settings
as
the collection databaseFlags
. For more information
about the representation of the flags in the output, see
Instances Resource Representation.
REST v1
To list flags configured for an instance:
Before using any of the request data, make the following replacements:
- project-id: The project ID
- instance-id: The instance ID
HTTP method and URL:
GET https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
In the output, look for the databaseFlags
field.
REST v1beta4
To list flags configured for an instance:
Before using any of the request data, make the following replacements:
- project-id: The project ID
- instance-id: The instance ID
HTTP method and URL:
GET https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
In the output, look for the databaseFlags
field.
Supported flags
Cloud SQL supports only those flags that are listed in this section.
Cloud SQL Flag | Type Acceptable Values and Notes |
Restart Required? |
---|---|---|
1204 (trace flag) | boolean on | off |
No |
1222 (trace flag) | boolean on | off |
No |
1224 (trace flag) | boolean on | off |
No |
2528 (trace flag) | boolean on | off |
No |
3205 (trace flag) | boolean on | off |
No |
3226 (trace flag) | boolean on | off |
No |
3625 (trace flag) | boolean on | off |
Yes |
4199 (trace flag) | boolean on | off |
No |
4616 (trace flag) | boolean on | off |
No |
7806 (trace flag) | boolean on | off |
Yes |
access check cache bucket count | integer 0 ... 65536 |
No |
access check cache quota | integer 0 ... 2147483647 |
No |
affinity mask | integer 2147483648 ... 2147483647 |
No |
agent xps | boolean on | off |
No |
automatic soft-numa disabled | boolean on | off |
Yes |
cloud sql xe bucket name | string The bucket name must start with the gs:// prefix. |
No |
cloud sql xe output total disk size (mb) | integer 10 ... 512 |
No |
cloud sql xe file retention (mins) | integer 0 ... 10080 |
No |
cloud sql xe upload interval (mins) | integer 1 ... 60 |
No |
cloudsql enable linked servers | boolean on | off |
No |
cost threshold for parallelism | integer 0 ... 32767 |
No |
contained database authentication | boolean on | off
|
No |
cross db ownership chaining | boolean on | off
|
No |
cursor threshold | integer -1 ... 2147483647 |
No |
default full-text language | integer 0 ... 2147483647 |
No |
default language | integer 0 ... 32 |
No |
default trace enabled | boolean on | off |
No |
disallow results from triggers | boolean on | off |
No |
external scripts enabled | boolean on | off |
Yes |
ft crawl bandwidth (max) | integer 0 ... 32767 |
No |
ft crawl bandwidth (min) | integer 0 ... 32767 |
No |
ft notify bandwidth (max) | integer 0 ... 32767 |
No |
ft notify bandwidth (min) | integer 0 ... 32767 |
No |
fill factor (%) | integer 0 ... 100 |
No |
index create memory (kb) | integer 704 ... 2147483647 |
No |
locks | integer 5000 ... 2147483647 |
Yes |
max server memory (mb) | integer 1000 ... 2147483647
Cloud SQL may set a value for this flag on instances, based on Microsoft's recommended values. For more information, see Special flags. |
No |
max text repl size (b) | integer -1 ... 2147483647 |
No |
max worker threads | integer 128 ... 65535 |
No |
nested triggers | boolean on | off |
No |
optimize for ad hoc workloads | boolean on | off |
No |
ph timeout (s) | integer 1 ... 3600 |
No |
query governor cost limit | integer 0 ... 2147483647 |
No |
query wait (s) | integer -1 ... 2147483647 |
No |
recovery interval (min) | integer 0 ... 32767 |
No |
remote access | boolean on | off |
Yes |
remote login timeout (s) | integer 0 ... 2147483647 |
No |
remote query timeout (s) | integer 0 ... 2147483647 |
No |
transform noise words | boolean on | off |
No |
two digit year cutoff | integer 1753 ... 9999 |
No |
user connections | integer 0 , 10 ... 32767 |
Yes |
user options | integer 0 ... 32767 |
No |
Special flags
This section contains additional information about Cloud SQL for SQL Server flags.
max server memory (mb)
This flag limits the amount of memory that Cloud SQL can allocate for its internal pools. The general recommendation is to set the value for the flag to about 80% to prevent SQL Server from consuming all of the available memory for the Cloud SQL instance. If you set the value to be greater than 80%, then you might experience instability, performance degradation, and database crashes because of out-of-memory issues.
If you don't set a value for this flag, then Cloud SQL manages the value automatically, based on the size of the RAM for your instance. Also, if you resize your instance, then Cloud SQL adjusts the value of the flag automatically to meet our recommendations for the new instance size. This ensures that your database utilizes resources effectively, preventing overallocation, crashes, and performance degradation for your instance.
Troubleshooting
Issue | Troubleshooting |
---|---|
You want to modify the time zone for a Cloud SQL instance. |
To see how to update an instance's time zone, see Instance settings. In Cloud SQL for SQL Server, you can use the |
What's next
- Learn more about SQL Server configuration options.