ApsaraDB RDS:Insufficient space issues for RDS for SQL Server

Analyze the cause

The total data space is the sum of all data file sizes and consists of two parts: allocated space and unallocated space.

• Allocated space includes used and unused space. Unused space can be allocated only to new records in the same table or index and cannot be used directly by other database objects.

• Unallocated space consists of completely unallocated extents. Each extent is 64 KB of contiguous space. Unallocated space is not associated with any database object. You can release this space by shrinking files.

If the data volume grows continuously, the unallocated space is typically small. In this scenario, shrinking files directly is not effective. You must first optimize and reclaim the allocated space before you consider shrinking the files.

Reclaim data space

The following methods are commonly used to reclaim data space:

• Archive data

  Delete infrequently used data, such as old historical data, from the database. You can also migrate the data to other database instances or archive it in other formats. This method directly reduces the data volume and the used data space.

  This method is effective for controlling data space growth but requires specific designs for database objects and application logic. It also requires cooperation from application designers and developers.

• Compress data

  Instances that run SQL Server 2016 or later and Enterprise instances that run a version earlier than 2016 have a built-in data compression feature. You can enable row or page compression on a single table, index, or partition. For more information, see Data Compression.

  The data compression ratio varies widely. It depends on the table schema, column data types, and value distribution. The ratio can range from 10% to 90%. SQL Server provides a dedicated stored procedure, sp_estimate_data_compression_savings, to help you quickly estimate the storage savings that result from enabling compression on a specific table or index.

  Note

  • Modifying compression settings on a table or index is a Data Definition Language (DDL) operation. For large tables, this operation can cause long-term table locks and affect your business. You should perform this operation during off-peak hours.

  • For an RDS for SQL Server Enterprise instance, you can set the ONLINE parameter to ON. Then, you can execute the modification. This has a minimal impact on your business.

  • Data compression increases CPU overhead. You should evaluate the impact as needed. Enable data compression only on large tables.

• Defragment indexes

  A high index fragmentation rate can slow down queries and increase data storage usage. Defragmenting indexes can improve query speed and release unnecessary storage space.

  View index fragmentation rate: In the RDS console, go to the Autonomy Services > Performance Optimization page. Click the Index Usage tab to view the index fragmentation rate for each table. The autonomy service provides suggestions to rebuild or reorganize indexes.

  Note

  • The index fragmentation rate is the percentage of logically contiguous index pages that are not physically contiguous. This is different from the percentage of free space in index pages. However, indexes with high fragmentation rates are also likely to have a high percentage of reclaimable space.

  • To analyze the average free space percentage within the pages of an index, you can query the sys.dm_db_index_physical_stats system view in SAMPLED or DETAILED mode. Then, check the value in the avg_page_space_used_in_percent column of the result set. For more information, see sys.dm_db_index_physical_stats (Transact-SQL). This query reads many index pages and may affect database performance. You should perform this operation during off-peak hours.

  • Index rebuild operation

    This operation provides better optimization and is more efficient for indexes with high fragmentation rates. By default, a rebuild operation locks the corresponding table. For Enterprise instances, you can set the ONLINE parameter to ON to avoid long-term table locks.

    Important

    For large indexes, a rebuild operation can cause a significant short-term increase in database storage space and log size. Before you rebuild an index, make sure the RDS instance has free storage space that is at least twice the size of the index that is being rebuilt.

    • View RDS instance storage space: Go to the Basic Information page of the RDS instance. In the Instance Resources section, you can view the total and used storage space of the instance.

    • If the free space is insufficient, you can perform a storage scale-out. After the scale-out is successful, you do not need to restart the instance. The system automatically applies the new space, and the change takes effect immediately.

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

    After the command is executed, the system asynchronously collects index statistics again in the background. This process takes some time. Therefore, the fragmentation rate displayed on the console page may not update immediately. You can click the Recollect button to manually collect the latest data. After data collection is complete, you can click Export Script to download the script to your local machine and confirm whether the index fragmentation rate has decreased.

    

  • Index reorganize operation

    This operation is more efficient for indexes with low fragmentation rates, but its optimization effect is not as significant as a rebuild.

Shrink data files

The data space reclamation methods described in the previous section can usually reduce data space usage effectively. If the instance space is still under pressure, you can use one of the following two methods:

• Method 1: Shrink the data file in a loop of mini-batches. We recommend that you shrink the file by 5 GB in each iteration. The following is an example:

  -- This script applies only to SQL Server 2012 and later versions. Specify the following parameters before use.
  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   -- The size to shrink in each loop iteration (MB). We recommend 5 GB.
  DECLARE @currentSize INT         -- Current size
  DECLARE @freeSize INT            -- Free space size
  DECLARE @usedSize INT            -- Used space size
  
  DECLARE @sql NVARCHAR(500)       
  DECLARE @waitTime INT = 10      -- Wait time after each shrink operation (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 the current size, free space size, and used space size
      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'
  
      -- Check if the target size is smaller than the used space 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 if the target size is reached
      IF @currentSize <= @targetSize
      BEGIN
          PRINT 'Shrink completed. Current size: ' + CAST(@currentSize AS VARCHAR(20)) + 'MB'
          BREAK
      END
      
      -- Calculate the new size after this shrink operation
      DECLARE @newSize INT = @currentSize - @shrinkSize
      IF @newSize < @targetSize
          SET @newSize = @targetSize
          
      -- Execute the shrink operation
      SET @sql = 'USE [' + @dbName + '];DBCC SHRINKFILE (N''' + @fileName + ''', ' + CAST(@newSize AS VARCHAR(20)) + ')'
      PRINT 'Executing shrink: ' + @sql
      EXEC (@sql)
      
      -- Wait for a period and then continue
      PRINT 'Waiting ' + CAST(@waitTime AS VARCHAR(10)) + ' seconds to continue...'
      WAITFOR DELAY '00:00:10'
  END

• Method 2: Execute the DBCC SHRINKFILE command to directly shrink a single data file. This command releases the unallocated space in the data file to the operating system. For more information, see Shrink a Database and DBCC SHRINKFILE (Transact-SQL).

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

  Click to view an example

  In the preceding figure, the size of an extent is 64 KB. The total size of the data file is (1673344 × 64) / 1024 = 104584 MB. The allocated space is (1313432 × 64) / 1024 = 82089.5 MB. This means the total size of the data file after compression cannot be less than 82089.5 MB. To shrink the total size of the data file to 90000 MB, you can execute the following command:

  DBCC SHRINKFILE(1, 90000)

FAQ

View used log space

Reclaiming log space is relatively simple. You can use the DBCC SQLPERF(LOGSPACE) command or DAS to view the percentage of used space in the database log file. If the percentage of used space is high, shrinking the log file has little effect. You can query the sys.databases system view. Check the output of the log_reuse_wait and log_reuse_wait_desc columns to determine why the space cannot be reclaimed.

Shrink transaction logs

After the shrink operation is complete, you can go to the Monitoring and Alarms page of the RDS instance to view the latest log space status.

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:

Analyze the cause

Other file space refers to the space that is occupied by files such as sqlserver.other_size, mastersize, modelsize, and msdbsize. These files are usually small, but in some cases, they can become very large. For example:

• There are many error logs (errorlog), and the error log file size grows to several gigabytes or even larger.

• Memory dump files are automatically generated during critical exceptions.

Solutions

1. Go to the Standard Monitoring tab on the Monitoring and Alarms page of the RDS instance to view the space that is occupied by these files. For more information about the metrics, see View standard monitoring.

   

2. If errorlog occupies a large amount of space, you can go to the Log Management page to clear the error logs. For more information, see Manage logs.

   Note

   If other files, such as sqlserver.other_size, occupy too much space, you can contact technical support. They will help you identify the cause and resolve the issue.