Wondering how to resolve Google Cloud SQL “Temporary file size exceeds temp_file_limit” error? We can help you.

Here at Bobcares, we often handle requests from our customers to fix similar errors as a part of our Server Management Services.

Today, we will see how our Support Engineers troubleshoot this error for our customers.

How to resolve Temporary file size exceeds temp_file_limit error?

Typical  error will look as shown below:

Temporary file size exceeds temp_file_limit

Usually, the error occurs if the temp_file_limit flag is set too low for your database usage.

To fix this error, we need to increase the temp_file_limit size.

Today, let us see the steps followed by our Support Techs to resolve this error.

Setting a database flag

• In the Google Cloud Console, create a new Cloud Console project, or open an existing project by selecting the project name.…see naming guidelines
• Next, open the instance and click Edit.
• Then, 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.
• Then, click Save to save your changes.
• Finally, confirm your changes under Flags on the Overview page.

Edit the instance:

gcloud sql instances patch [INSTANCE_NAME] --database-flags [FLAG1=VALUE1,FLAG2=VALUE2]

The above 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 (“=”).

Clearing all flags to their default value

• In the Google Cloud Console, create a new Cloud Console project, or open an existing project by selecting the project name.…see naming guidelines
• Then, open the instance and click Edit.
• Next, open the Database flags section.
• Click the X next to all of the flags shown.
• Finally, click Save to save your changes.

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

Viewing current values of database flags

To view all current values of the PostgreSQL settings, log into your instance with the psql client and enter the following statement:

SELECT name, setting FROM pg_settings;

Determine the database flags that are set for an instance

To see what flags are set for a Cloud SQL instance:

• In the Google Cloud Console, create a new Cloud Console project, or open an existing project by selecting the project name.…see naming guidelines
• Then, select the instance to open its Instance Overview page.The database flags set are list under the Database flags section.

Get the instance state:

gcloud sql instances describe [INSTANCE_NAME]

In the output, database flags are list under the settings as the collection databaseFlags.

[Need assistance with google cloud error? We can help you]

Conclusion

In short, today we saw how our Support Techs resolved Google Cloud SQL “Temporary file size exceeds temp_file_limit” error