Configure database flags

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

  1. In the Google Cloud console, select the project that contains the Cloud SQL instance for which you want to set a database flag.
  2. Open the instance and click Edit.
  3. Scroll down to the Flags section.
  4. 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.
  5. Click Save to save your changes.
  6. 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.

resource "google_sql_database_instance" "instance" {
  name             = "sqlserver-instance-flags"
  region           = "us-central1"
  database_version = "SQLSERVER_2019_STANDARD"
  root_password    = "INSERT-PASSWORD-HERE"
  settings {
    database_flags {
      name  = "1204"
      value = "on"
    }
    database_flags {
      name  = "remote access"
      value = "on"
    }
    database_flags {
      name  = "remote query timeout (s)"
      value = "300"
    }
    tier = "db-custom-2-7680"
  }
  # set `deletion_protection` to true, will ensure that one cannot accidentally delete this instance by
  # use of Terraform whereas `deletion_protection_enabled` flag protects this instance at the GCP level.
  deletion_protection = false
}

Apply the changes

To apply your Terraform configuration in a Google Cloud project, complete the steps in the following sections.

Prepare Cloud Shell

  1. Launch Cloud Shell.
  2. 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).

  1. In Cloud Shell, create a directory and a new file within that directory. The filename must have the .tf extension—for example main.tf. In this tutorial, the file is referred to as main.tf.
    mkdir DIRECTORY && cd DIRECTORY && touch main.tf
  2. 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.

  3. Review and modify the sample parameters to apply to your environment.
  4. Save your changes.
  5. 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

  1. 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.

  2. 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.

  3. 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:

  1. To disable deletion protection, in your Terraform configuration file set the deletion_protection argument to false.
    deletion_protection =  "false"
  2. Apply the updated Terraform configuration by running the following command and entering yes at the prompt:
    terraform apply
  1. 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

  1. In the Google Cloud console, select the project that contains the Cloud SQL instance for which you want to clear all flags.
  2. Open the instance and click Edit.
  3. Open the Database flags section.
  4. Click the X next to all of the flags shown.
  5. 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

  1. 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.
  2. 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 AT TIME ZONE function for time conversions and more. For more information about this function, see AT TIME ZONE (Transact-SQL).

What's next