Solves the problem that SQL Server instances are locked when the storage space is full - ApsaraDB RDS

Description

Your apsaradb RDS for SQL Server instance may run low space due to attacks such as SQL statements and external data. To prevent data loss, apsaradb RDS for SQL Server automatically locks the instance. After the disk is locked, the write operation will fail. When the instance is automatically locked because the instance space is full, in the console, running status the following information is displayed.

Causes

There are three main reasons for the full space of SQL Server instance:

Log File usage is high.
High Data File usage
High temporary file usage

Solutions

Note

Alibaba Cloud reminds you that:

When you perform operations that have risks, such as modifying instance configurations or data, check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
Before you modify the configurations and data of instances including but not limited to ECS and RDS instances, we recommend that you create snapshots or enable RDS log backup.
If you have authorized or submitted security information such as the logon account and password in the Alibaba Cloud Management console, we recommend that you modify such information in a timely manner.

View space usage

Method 1

View the space usage on the monitoring page of the RDS console. For more information, see view resource and engine monitoring data.

The following table describes the parameters in the naming conventions.

Parameter	Description
The size of consumed disk space.	Size of data files and log files for all user databases.
The data size.	The size of the data files (mdf and ndf files) for all user databases.
The size of consumed log space.	Log files (ldf files) for all user databases.
Temporary file size	The size of all mdf, ndf, and ldf files for tempdb.
System File space usage	Data files and log files for master, msdb, and model databases, as well as the size of some system files (error logs, dll files, etc.) under the SQL Server instance directory.

Method 2

Run SQL statements to view the sizes of data files (mdf and ndf files) and log files (ldf files) in all databases. For more information, see RDS for SQL Server how to view the storage space occupied by instances, databases, and tables.

Solve the problem of full space and automatic locking

Upgrade the storage space of an instance

You can unlock an instance after upgrading its storage space. For more information about how to upgrade the storage space of an instance, see change configuration if the instance storage space has reached the maximum limit, submit a ticket to contact customer service to temporarily unlock the instance.

Solution to high log file usage

Causes

If the application has a large number of transaction operations, the transaction logs continue to grow, and the instance disk space may exceed the upper limit, resulting in the instance being locked.

Solution 1

After the client connects to the instance, run the following statement:
```
select name,log_reuse_wait,log_reuse_wait_desc from sys.databases;
```
RuO log_reuse_wait_desc the value of IS LOG_BACKUP, please shrink transaction log.
Note
Instructions if the log file is very large, the log backup time is relatively long, and when shrinking the log file, if it encounters uncommitted transactions, the effect of the single shrinkage is not obvious. In cases where the effect of a single shrink is not obvious, it is recommended that you shrink the transaction log again.

Solution 2

The root cause of rapid growth of transaction logs is the large number of transactions or large transactions. For example, 5 million rows of data are operated in a transaction. If such a large transaction exists, we recommend that you split the transaction and execute each transaction in 100,000 rows in 50 times.

Solution to high data file usage If database files occupy a large amount of space, you can check the data file usage. For databases with large files but low usage, you can handle them accordingly. Detailed steps are as follows:
1. Run the following SQL statements in sequence to check the free space in the database:
```
USE [$DB_Name];
SELECT SUM(unallocated_extent_page_count) AS [free pages],
       (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage
```
  Note:[$DB_Name] indicates a database name.
2. Find a database that consumes a large amount of space and run the following statement to shrink the database:
```
DBCC SHRINKDATABASE([$DB_Name]);
```
  You can also run the following command to shrink a single file.
```
DBCC SHRINKFILE(file_id,[$Size]);
```
  Note
  Note:[$Size] indicates the Size after shrinkage, not the Size to be shrunk. The unit is MB.

Solution to high usage of temporary files. From the instance monitoring tab, you can determine whether the temporary files consume too much space. If there is not enough space for temporary files, the corresponding Error Log is also recorded. For information about how to troubleshoot the situation where temporary file space is insufficient, see Troubleshooting Insufficient Disk Space in tempdb, we recommend that you perform the following operations.
- Restart the instance to release space for temporary files.
- Releases temporary tables, row versions, and table variables in a timely manner.

Application scope

ApsaraDB RDS for SQL Server