ApsaraDB RDS:RDS SQL Server disk space full issue

Archive historical data

Delete, migrate to another RDS instance, or archive historical data that is no longer frequently queried. This reduces the total data stored on the instance.

This method is effective at reducing long-term growth, but requires changes to application logic and cooperation with application designers and developers.

Enable data compression

Data compression is available for:

• SQL Server 2016 or later

• SQL Server Enterprise Edition earlier than SQL Server 2016

Compression supports row compression and page compression, and can be applied to individual tables, indexes, or partitions. The compression ratio ranges from 10% to 90%, depending on the schema, column data types, and value distribution.

Use the sp_estimate_data_compression_savings stored procedure to estimate how much space compression will save on a specific table or index before enabling it.

Enabling or changing compression requires data definition language (DDL) operations, which lock the table during execution. Run these operations during off-peak hours.

On SQL Server Enterprise Edition, set the ONLINE parameter to ON to avoid long-term table locking.

Data compression increases CPU overhead. Enable compression only on large tables where the storage savings justify the CPU cost.

For details, see the Microsoft documentation on Data compression.

Defragment indexes

High index fragmentation causes the index to consume more storage than its actual data requires. Defragmenting reclaims that excess allocated space.

View index fragmentation: In the left-side navigation pane, choose Autonomy Services > Performance Optimization, then click the Index Usage tab. This tab shows fragmentation levels per table and suggestions to rebuild or reorganize specific indexes.

Fragmentation rate measures the percentage of pages whose logical order does not match their physical order — not the percentage of empty space per page. To analyze average idle space per page, query sys.dm_db_index_physical_stats in SAMPLED or DETAILED mode and check the avg_page_space_used_in_percent column. See sys.dm_db_index_physical_stats (Transact-SQL).

Choose the method based on the fragmentation level:

• Rebuild (high fragmentation): Produces better results and reorganizes storage more completely. By default, the table is locked during the rebuild. On SQL Server Enterprise Edition, set ONLINE = ON to allow concurrent access.

  Important

  An index rebuild can cause a significant, short-term increase in both data storage and log size. Reserve at least twice the size of the index being rebuilt as available storage before starting. Check available space on the Basic Information page under Instance Resources.

  ALTER INDEX <IX_YourIndexName> ON <YourTableName> REBUILD WITH (ONLINE = ON);

  After the rebuild completes, the console updates fragmentation data asynchronously. Click Recollect to pull the latest data immediately, then click Export Script to download the results for verification.

  

• Reorganize (low fragmentation): Less intrusive than a rebuild but also less effective. Use this when fragmentation is low and you cannot accept a table lock.

Run defragmentation operations during off-peak hours — reading many index pages during a defragmentation operation can degrade query performance.

Shrink data files

Capture a baseline before shrinking. Run the following query to record current file sizes. Run it again after the shrink to confirm the result.

SELECT
    file_id,
    name,
    CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8 / 1024.0 AS space_used_mb,
    CAST(size AS bigint) * 8 / 1024.0 AS space_allocated_mb
FROM sys.database_files
WHERE type_desc = 'ROWS';

Method 1 — Batch shrink (recommended): Shrink 5 GB at a time to limit the impact on transaction logs and service availability.

DECLARE @dbName NVARCHAR(128) = 'YourDatabaseName'  -- Database name
DECLARE @fileName NVARCHAR(128)  -- Data file name
DECLARE @targetSize INT = 1024   -- Target size (MB)
DECLARE @shrinkSize INT = 5120   -- Amount to shrink per iteration (MB); 5 GB recommended
DECLARE @currentSize INT         -- Current size
DECLARE @sql NVARCHAR(500)
DECLARE @waitTime INT = 10       -- Wait time between iterations (seconds)

-- Get the data file name
SELECT @fileName = name
FROM sys.master_files
WHERE database_id = DB_ID(@dbName)
AND type_desc = 'ROWS'

-- Shrink in a loop
WHILE 1 = 1
BEGIN
    -- Get current file size
    SELECT @currentSize = size/128
    FROM sys.database_files
    WHERE name = @fileName

    -- Stop when the target size is reached
    IF @currentSize <= @targetSize
    BEGIN
        PRINT 'Shrink complete, current size: ' + CAST(@currentSize AS VARCHAR(20)) + 'MB'
        BREAK
    END

    -- Calculate the new target for this iteration
    DECLARE @newSize INT = @currentSize - @shrinkSize
    IF @newSize < @targetSize
        SET @newSize = @targetSize

    -- Run the shrink
    SET @sql = 'DBCC SHRINKFILE (N''' + @fileName + ''', ' + CAST(@newSize AS VARCHAR(20)) + ')'
    PRINT 'Executing shrink: ' + @sql
    EXEC (@sql)

    -- Wait before the next iteration
    PRINT 'Waiting ' + CAST(@waitTime AS VARCHAR(10)) + ' seconds before continuing...'
    WAITFOR DELAY '00:05:00'
END

Method 2 — Shrink a single file: Run DBCC SHRINKFILE directly to shrink one data file to a target size.

DBCC SHRINKFILE(<File ID>, <Expected file size after shrink in MB>)

Before running this command, check how much space is allocated versus used. The minimum target size is the allocated space, not the used space. For example, if sys.database_files shows a total of 1,673,344 pages and 1,313,432 allocated pages, the extent size is 64 KB:

• Total size: (1,673,344 x 64) / 1,024 = 104,584 MB

• Minimum target (allocated): (1,313,432 x 64) / 1,024 = 82,089.5 MB

To shrink to 90,000 MB:

DBCC SHRINKFILE(1, 90000)

For reference, see Shrink a database and DBCC SHRINKFILE (Transact-SQL).

FAQ

After running an index rebuild on a high-fragmentation index, why does the fragmentation rate in the Index Usage tab remain unchanged?

The console updates fragmentation data asynchronously after a rebuild — this is expected. Click Recollect to trigger an immediate data collection. Once complete, click Export Script to download the results and verify that fragmentation has decreased.

Check why log space cannot be reclaimed

Run DBCC SQLPERF(LOGSPACE) or check the Storage Management page to see the percentage of used log storage. If the percentage is high, shrinking the log files will have almost no effect — the issue is that the log space cannot yet be truncated, not that the log file is too large.

Query sys.databases to find out what is blocking truncation:

SELECT name, log_reuse_wait, log_reuse_wait_desc
FROM sys.databases;

For the full list of values, see sys.databases (Transact-SQL).

Shrink transaction logs

Do not shrink transaction logs as a routine maintenance operation. Log files that grow due to regular business activity will simply re-grow after shrinking.

Choose the method based on your situation:

After shrinking, go to the Monitoring and Alerts page to verify that log storage usage has decreased.

How tempdb grows

Temporary file storage is the space used by the tempdb system database. tempdb uses only the SIMPLE recovery model, so its log files stay small. However, tempdb data files can grow rapidly when queries create many temporary tables, join large tables, or sort large datasets.

Reduce tempdb usage

• At the application level: reduce unnecessary temporary tables, avoid large table joins where possible, and break large transactions into smaller ones.

• Restart the RDS instance during off-peak hours. After a restart, tempdb returns to the size it had when the instance was first created.

What counts as other file storage

Other file storage includes space used by sqlserver.other_size, mastersize, modelsize, msdbsize, and similar system files. These files are normally small, but can grow in two situations:

• Many errorlog files have accumulated and grown to several GB or larger.

• Memory dump files were generated during a severe exception.

Reduce other file storage

1. Go to the Monitoring and Alerts page and open the Standard Monitoring tab to see how much space each file type is consuming. For metric descriptions, see View standard monitoring information.

   

2. If errorlog files are consuming significant space, clear them on the Log Management page. For details, see Manage logs.

If sqlserver.other_size or other system files are consuming an unexpectedly large amount of space, contact the helpdesk. They will help identify the root cause.