ApsaraDB RDS:Insufficient space issues for RDS for SQL Server

Cause analysis

Solutions

An RDS for SQL Server database contains data files and log files. The methods to reclaim space for each are as follows:

How data space works

Total data space equals the sum of all data file sizes, split into two parts:

• Allocated space: Space assigned to tables or indexes. Includes both rows with data and pages reserved for future inserts into the same table or index. Other objects cannot use this space directly.

• Unallocated space: Completely free extents (each extent is 64 KB of contiguous space) not associated with any object. Shrinking a file releases this space back to the OS.

If data is still growing, unallocated space is typically small. Shrinking files directly has little effect. Reclaim allocated space first, then consider shrinking.

Reclaim allocated space

Defragment indexes

High index fragmentation slows queries and wastes storage. To view fragmentation rates, go to Autonomy Services > Performance Optimization and click the Index Usage tab. The console shows the fragmentation rate per table and suggests whether to rebuild or reorganize each index.

Index fragmentation rate measures the percentage of logically adjacent index pages that are not physically adjacent. This differs from the percentage of free space within pages. To measure average free 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. For details, see sys.dm_db_index_physical_stats (Transact-SQL). This query reads many index pages — run it during off-peak hours.

Index rebuild (REBUILD): Completely recreates the index. More effective than reorganize for indexes with high fragmentation. By default, locks the table during the operation. On Enterprise Edition instances, set ONLINE = ON to avoid long table locks.

Important

A rebuild temporarily increases database storage and log size significantly. Before rebuilding, verify that the instance has free storage space at least twice the size of the index being rebuilt.

• Check free space: go to the Basic Information page of the instance and look at the Instance Resources section.

• If free space is insufficient, scale out storage first. The new space takes effect immediately without a restart.

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

After the rebuild, the console updates index statistics asynchronously. Click Recollect to get the latest data, then click Export Script to download and verify the fragmentation rate.

Index reorganize (REORGANIZE): Restructures leaf-level pages without locking the table. Less effective than rebuild, but appropriate for indexes with low fragmentation.

Shrink data files

After reclaiming allocated space using the methods above, use one of the following approaches if storage pressure remains.

Method 1 — Incremental shrink loop (recommended)

Shrink in iterations of approximately 5 GB each. The following script applies to SQL Server 2012 and later:

-- This script applies only to SQL Server 2012 and later versions. Specify the following parameters before running.
DECLARE @dbName NVARCHAR(128) = 'YourDBName'  -- Database name
DECLARE @fileName NVARCHAR(128)               -- Data file name
DECLARE @targetSize INT = 2000                -- Target size (MB)
DECLARE @shrinkSize INT = 5120                -- Size to shrink per iteration (MB). 5 GB recommended.
DECLARE @currentSize INT                      -- Current file size
DECLARE @freeSize INT                         -- Unallocated space
DECLARE @usedSize INT                         -- Used space

DECLARE @sql NVARCHAR(500)
DECLARE @waitTime INT = 10                    -- Wait 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 size and space breakdown
    DECLARE @sql0 NVARCHAR(MAX) = N'
    USE [' + @dbName + '];
    SELECT
      @currentSize = (SUM(total_page_count) * 1.0 / 128),
      @freesize = (SUM(unallocated_extent_page_count) * 1.0 / 128)
    FROM sys.dm_db_file_space_usage
    WHERE database_id = DB_ID();'

    EXEC sp_executesql
      @sql0,
      N'@currentSize INT OUTPUT, @freesize INT OUTPUT',
      @currentSize OUTPUT,
      @freesize OUTPUT

    PRINT 'Current size:' + CAST(@currentSize AS VARCHAR(10)) + 'MB'
    PRINT 'Free size:' + CAST(@freeSize AS VARCHAR(10)) + 'MB'

    SET @usedSize = @currentSize - @freeSize
    PRINT 'Used size:' + CAST(@usedSize AS VARCHAR(10)) + 'MB'

    -- Validate target size
    IF @targetSize <= @usedSize
    BEGIN
        PRINT 'The target size is too small. Specify a new size. The target size cannot be smaller than: ' + CAST(@usedSize AS VARCHAR(20)) + 'MB'
        BREAK
    END

    -- Exit when target is reached
    IF @currentSize <= @targetSize
    BEGIN
        PRINT 'Shrink completed. Current size: ' + CAST(@currentSize AS VARCHAR(20)) + 'MB'
        BREAK
    END

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

    -- Run the shrink
    SET @sql = 'USE [' + @dbName + '];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 to continue...'
    WAITFOR DELAY '00:00:10'
END

Method 2 — Direct DBCC SHRINKFILE

Run DBCC SHRINKFILE to shrink a single data file to a target size. This releases unallocated space back to the OS. For reference, see Shrink a database and DBCC SHRINKFILE.

DBCC SHRINKFILE(<File ID>, <Target size in MB>)

Click to view an example

Example: In the figure below, each extent is 64 KB. Total data file size = (1673344 × 64) / 1024 = 104,584 MB. Allocated space = (1313432 × 64) / 1024 = 82,089.5 MB. The file cannot be shrunk below 82,089.5 MB. To shrink to 90,000 MB, run:

DBCC SHRINKFILE(1, 90000)

Check log space usage

Use DBCC SQLPERF(LOGSPACE) or the Monitoring and Alerts page to check the percentage of used space in each database's log file.

If the used percentage is high, shrinking the log file will have little effect. The log cannot be truncated until the blocking condition is resolved. Query sys.databases and check the log_reuse_wait_desc column to identify why truncation is waiting:

SELECT name, log_reuse_wait, log_reuse_wait_desc FROM sys.databases;

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

Shrink transaction logs

Choose the approach that fits your situation:

After the shrink completes, go to Monitoring and Alerts to verify the updated log space.

How tempdb uses space

tempdb is a SQL Server system database that stores temporary data. Three categories of objects consume its data file space:

• User objects: Temporary tables created by user sessions (for example, #TempTable).

• Internal objects: Temporary structures created by SQL Server internally (for example, for sort operations or hash joins).

• Version store: When snapshot isolation or read committed snapshot isolation (RCSI) is enabled, SQL Server stores row versions in tempdb. Long-running transactions prevent these versions from being cleaned up.

If any of these categories grows large — especially through long-running transactions, heavy use of temporary tables, or snapshot isolation — the tempdb data file can become bloated far beyond its normal size. For background, see the Microsoft tempdb documentation.

Reclaim tempdb data file space

SHRINKFILE is generally ineffective for tempdb data files. The most reliable way to reclaim space is to restart the instance during off-peak hours. SQL Server resets tempdb on startup. For details, see Shrink the tempdb database.

Before restarting, diagnose what caused the growth so you can prevent recurrence.

Scenario 1: tempdb is currently large (live instance)

Step 1 — Check space by object type

Run the following query to see how much space each category (user objects, internal objects, version store) is consuming:

SELECT
    SUM(version_store_reserved_page_count) AS [version store pages used],
    (SUM(version_store_reserved_page_count) * 1.0 / 128) AS [version store space (MB)],
    SUM(user_object_reserved_page_count) AS [user object pages used],
    (SUM(user_object_reserved_page_count) * 1.0 / 128) AS [user object space (MB)],
    SUM(internal_object_reserved_page_count) AS [internal object pages used],
    (SUM(internal_object_reserved_page_count) * 1.0 / 128) AS [internal object space (MB)]
FROM
    sys.dm_db_file_space_usage;

For reference, see sys.dm_db_file_space_usage.

Step 2a — If user objects or internal objects are large

Find which sessions are using the most tempdb space:

SELECT
    session_id,
    SUM(user_objects_alloc_page_count) AS [user object pages used],
    (SUM(user_objects_alloc_page_count) * 1.0 / 128) AS [user object space (MB)],
    SUM(internal_objects_alloc_page_count) AS [internal object pages used],
    (SUM(internal_objects_alloc_page_count) * 1.0 / 128) AS [internal object space (MB)]
FROM
    sys.dm_db_session_space_usage
GROUP BY
    session_id;

For reference, see sys.dm_db_session_space_usage.

Then use the session_id to find the SQL statement the session is running:

SELECT
    r.session_id AS [SPID],
    r.start_time AS [Start Time],
    r.status AS [Status],
    r.command AS [Command Type],
    r.wait_type AS [Wait Type],
    r.wait_time AS [Wait Time (ms)],
    r.last_wait_type AS [Last Wait Type],
    t.text AS [Executing Statement]
FROM
    sys.dm_exec_requests r
CROSS APPLY
    sys.dm_exec_sql_text(r.sql_handle) t
WHERE
    r.session_id = <session_id>; -- Replace with the session_id from the previous query

Step 2b — If the version store is large

The database likely has snapshot isolation enabled with long-running transactions preventing cleanup.

Check which databases have snapshot isolation enabled:

SELECT
    name,
    is_read_committed_snapshot_on,
    snapshot_isolation_state
FROM sys.databases;

If is_read_committed_snapshot_on or snapshot_isolation_state is 1, snapshot isolation is active.

Find sessions with long-running uncommitted transactions that are blocking version store cleanup:

SELECT * FROM sys.dm_tran_active_snapshot_database_transactions ORDER BY elapsed_time_seconds DESC;

Use the session_id to check what the session is doing:

SELECT * FROM sys.sysprocesses WHERE spid = <session_id>;

If the session status is sleeping, check the last executed statement:

DBCC INPUTBUFFER(<session_id>);

Scenario 2: Investigating past tempdb growth after a restart

If the instance has already been restarted and live data is unavailable, use Average Active Sessions (AAS), slow query logs, and audit logs to reconstruct what happened.

Step 1 — Find the growth window

On the instance details page, go to Monitoring and Alerts > Standard Monitoring. In the Instance Storage section, note when the tmp_size metric started and stopped rising. Keep in mind that tempdb may reuse existing free space first — actual file expansion happens only after existing space is exhausted, so the growth in the chart may lag behind the start of the SQL operation.

Step 2 — Analyze using AAS

Go to Autonomy Services > Performance Optimization > Performance Insight. Select the time window, extending the start time earlier to capture all related operations. Look for SQL statements that heavily use disk-based temporary tables (for example, frequent creation of # tables).

Step 3 — Filter slow query logs by keyword

Go to Autonomy Services > Slow Query Logs. Search by keyword (for example, a temporary table name). Check query start times and durations to see whether the query end time aligns with when tempdb stopped growing.

Reclaim tempdb log file space

tempdb log files grow when a long-running transaction prevents log truncation.

1. Check log_reuse_wait_desc in sys.databases:

   SELECT
       name AS [DatabaseName],
       recovery_model_desc AS [RecoveryModel],
       log_reuse_wait_desc AS [LogReuseWaitDescription]
   FROM sys.databases;

   If LogReuseWaitDescription is NOTHING, shrink the log file directly with SHRINKFILE. If it shows ACTIVE_TRANSACTION, end the active long-running transaction first.

   

2. Find the longest-running transaction in tempdb:

   USE tempdb;
   GO
   DBCC OPENTRAN;
   GO

   Note the session ID (SPID) and transaction start time.

   

3. Check what the session is doing:

   SELECT * FROM sys.sysprocesses WHERE spid = <spid>;

   

4. If the session status is sleeping, check its last executed statement:

   DBCC INPUTBUFFER(<session_id>);

   

After ending the blocking transaction, shrink the tempdb log file:

USE tempdb;
DBCC SHRINKFILE(templog, 1);
GO

What other file space includes

Other file space covers system database files: sqlserver.other_size, mastersize, modelsize, and msdbsize. These are usually small, but can grow unexpectedly in two scenarios:

• Error logs: The errorlog file can grow to several gigabytes or more if the instance generates many error events.

• Memory dump files: SQL Server automatically generates memory dumps during critical exceptions.

Identify and clear oversized files

1. Go to Monitoring and Alerts > Standard Monitoring and check the space used by these file types. For metric definitions, see View standard monitoring.

   

2. If errorlog is consuming a large amount of space, go to the Logs page to clear the error logs. For instructions, see Manage logs.

If other system files (such as sqlserver.other_size) are unexpectedly large, contact technical support to identify the root cause.