When an ApsaraDB RDS for SQL Server instance runs out of disk space, the instance is automatically locked to prevent data loss. Once locked, all write operations fail, and the running status indicator in the RDS console reflects the locked state.
Storage space can fill up for three reasons:
High log file usage — transaction logs grow continuously due to a large volume of transactions or very large individual transactions.
High data file usage — database data files accumulate allocated space that is not automatically reclaimed after row deletions.
High temporary file usage — tempdb uses excessive space.
This document explains how to identify which cause applies to your instance and how to resolve it.
Check space usage
Two methods are available. Use Method 1 for a quick visual overview, and Method 2 for precise per-database file sizes.
Method 1: View the monitoring page
On the instance monitoring page, check the following metrics. For details, see View standard monitoring.
| Metric | What it measures |
|---|---|
| The size of consumed disk space | Data files and log files for all user databases |
| The data size | Data files (.mdf and .ndf) for all user databases |
| The size of consumed log space | Log files (.ldf) for all user databases |
| Temporary file size | All .mdf, .ndf, and .ldf files for tempdb |
| System file space usage | Data and log files for master, msdb, and model, plus system files (error logs, DLL files, and similar) under the SQL Server instance directory |
Method 2: Run a SQL query
Connect to the instance and run SQL statements to check the exact size of data files (.mdf and .ndf) and log files (.ldf) across all databases. For details, see How do I view the size of an RDS for SQL Server database and the tables in the database.
Resolve the lock
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.
Upgrade storage space
Upgrading storage space unlocks the instance immediately. See Change the specifications of an ApsaraDB RDS for SQL Server instance for steps.
If the instance has already reached the maximum storage limit, submit a ticket to temporarily unlock it while you free up space.
Resolve high log file usage
Diagnose the cause
Connect to the instance and run the following query:
SELECT name, log_reuse_wait, log_reuse_wait_desc FROM sys.databases;If the log_reuse_wait_desc value is LOG_BACKUP, shrink the transaction log (see below).
Shrink the transaction log (when log_reuse_wait_desc is LOG_BACKUP)
To shrink the transaction log, follow the instructions in How do I shrink the transaction logs of an ApsaraDB RDS for SQL Server instance.
If the log file is very large, the backup takes longer. If uncommitted transactions exist at the time of shrinking, a single shrink pass may not reclaim all space. Run the shrink operation again after the backup completes.
Prevent recurrence: split large transactions
Rapid log growth is almost always caused by a high volume of transactions or very large individual transactions. For example, if a single transaction updates 5 million rows, split it into 50 batches of 100,000 rows each. Smaller transactions produce less log growth and allow more frequent truncation.
Resolve high data file usage
Data files do not automatically shrink when rows are deleted — allocated space stays allocated. Check which databases hold significant unused space, then shrink selectively.
Step 1: Identify databases with high free space
Run the following query for each database to see how much space is allocated but unused:
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;Replace $DB_Name with the target database name.
Step 2: Shrink the database or a specific file
To shrink an entire database:
DBCC SHRINKDATABASE([$DB_Name]);To shrink a single file:
DBCC SHRINKFILE(file_id, [$Size]);Replace $Size with the target size after shrinking (not the amount to shrink), in MB.
Resolve high temporary file usage
Diagnose the cause
On the instance monitoring tab, check temporary file consumption. When tempdb runs low on space, the corresponding error is also recorded in the error log.
For a detailed troubleshooting guide, see the Microsoft documentation on Troubleshooting insufficient disk space in tempdb.
Resolution
Restart the instance to release temporary file space immediately.
Release temporary tables, row versions, and table variables promptly in application code to prevent tempdb from growing unnecessarily.
Application scope
ApsaraDB RDS for SQL Server