How to configure and clean error logs in Cloud SQL for SQL Server

[mcanaleta]

Hi, for some reasons, we have a huge amount of error logs, like 500K lines per day. The problem is that the database is keeping logs since day 1, and there seems to be no way of cleaning them or configuring the maximum number of files and size.

None of the options described in https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/scm-services-configure-sql-s... are available, probably because of limited permissions to the underlying instance.

I suspect that these logs are consuming space of the sql server instance, because the space observed in gcloud monitoring is like 5 times larger than our actual accumulated tables sizes.

Any solution or workaround?

Thanks.

[christianpaula]

Hi @mcanaleta,

Welcome to Google Cloud Community!

There are a few ways you can manage the error logs in Cloud SQL for SQL Server:

1. You can use the sp_cycle_errorlog stored procedure to recycle the error logs. This procedure closes the current error log file and starts a new one. You can set the @cycle_errorlog parameter to "1" to recycle the error logs every time the procedure is executed.
2. You can use the SQL Server Management Studio to delete the error logs. To do this, connect to the Cloud SQL instance and expand the "Management" folder. Right-click on the "SQL Server Logs" folder and select "Delete". This will delete all of the error logs.
3. You can also configure the maximum number of error log files and the maximum size of each error log file. To do this, you will need to execute the following T-SQL code:

EXEC sp_configure 'max error log size', '{size_in_mb}'
RECONFIGURE
EXEC sp_configure 'max number of error log files', '{number_of_files}'
RECONFIGURE

Replace {size_in_mb} with the maximum size of each error log file in megabytes, and {number_of_files} with the maximum number of error log files.

Thank you

[mcanaleta]

Hi @christianpaula! Thanks for your reply. We already tried the solutions you suggest without success, because of permissions:

1. Permission denied
2. This option does not even show in the menu.
3. Again, permission denied

I understand managing the error log requires sysadmin role in SQL Server, and acording to the Cloud SQL documentation (https://cloud.google.com/sql/docs/sqlserver/users) The sysadmin role is not supported.

Thanks.