ApsaraDB RDS:RDS SQL Server disk space full issue

Analysis

The total data storage, which is equal to the total size of data files, consists of allocated data storage and unallocated data storage:

• The allocated data storage consists of used data storage and unused data storage. The unused data storage can be allocated only to new records in the same table or index. The unused data storage cannot be directly allocated to other database objects.

• The unallocated data storage consists of extents that are not allocated. Each extent provides 64 KB of contiguous storage. The unallocated data storage is not associated with database objects. You can shrink data files to release the unallocated data storage.

In most cases, if the amount of your data continues to increase, the unallocated data storage is small. In this case, you cannot reclaim a large amount of data storage by shrinking data files. Before you shrink data files, we recommend that you optimize and reclaim the allocated data storage.

Reclaim data storage

You can use one of the following methods to reclaim the data storage:

• Archive data files

  Delete historical data files that are not frequently queried, migrate these data files to other RDS instances, or archive these data files. This reduces the amount of data that is stored on your RDS instance.

  This method effectively mitigates increases in data storage consumption. However, this method has requirements for the database object structure and the logic of your application. This method also requires cooperation with application designers and developers.

• Compress data files

  If your RDS instance runs SQL Server 2016 or later or runs an SQL Server Enterprise Edition earlier than SQL Server 2016, the data compression feature is provided. This feature supports row compression and page compression. You can enable this feature on individual tables, indexes, or partitions. For more information, see Data Compression.

  The data compression ratio ranges from 10% to 90% and varies based on the schema, column data types, and distribution of numerical values. SQL Server provides a dedicated stored procedure sp_estimate_data_compression_savings that can help you quickly evaluate how much data storage space can be saved by enabling compression on a specified table or index.

  Note

  • To modify the compression option settings of tables or indexes, you must run data definition language (DDL) operations. If you run these operations on large tables, the tables are locked for a long period of time. This may interrupt your workloads. We recommend that you modify the compression option settings during off-peak hours.

  • RDS SQL Server Enterprise instances can set the parameter ONLINE to ON, and then perform modification operations with minimal impact on normal business operations.

  • Data compression increases CPU overhead. You must evaluate the feasibility of data compression on your RDS instance based on your business requirements. We recommend that you enable the data compression feature only on large tables.

• Defragment indexes

  If the degree of fragmentation in an index is high, the underlying data of the index consumes an abnormally large amount of storage. In this case, you can defragment the index to reduce the amount of used data storage.

  View the degree of fragmentation in indexes: Log on to the ApsaraDB RDS console and choose Autonomy Services > Performance Optimization. Then, click the Index Usage tab. On this tab, you can view the index fragmentation in various tables. In addition, you can view the suggestions that are proposed by the autonomy service to rebuild or reorganize indexes.

  Note

  • The degree of fragmentation in an index represents the percentage of pages whose logical ordering does not match the physical ordering inside the index. This percentage is different from the percentage of idle storage space in index pages. In normal cases, an index with a high degree of fragmentation is likely to be defragmented to reclaim storage space.

  • If you want to analyze the average percentage of idle storage per page in an index, you can query the sys.dm_db_index_physical_stats system view in SAMPLED or DETAILED mode. Then, you can view the values in the avg_page_space_used_in_percent column of the returned result. For more information, see sys.dm_db_index_physical_stats (Transact-SQL). The system reads many index pages during queries. If you defragment indexes during queries, the performance of your RDS instance may decrease. We recommend that you defragment indexes during off-peak hours.

  • Rebuild an index

    This method provides better optimization results and higher execution efficiency for high fragmentation rates. By default, the table is locked during execution. In the Enterprise Edition, you can set the parameter ONLINE to ON to avoid long-term table locking.

    Important

    For large indexes, the rebuild operation may cause a significant increase in database storage space and log size in a short period of time. Typically, before performing an index rebuild operation, your RDS instance needs to reserve at least twice the size of the index being rebuilt as available space. Therefore, make sure that your instance has sufficient available storage space.

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

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

    After the command is executed, the system will asynchronously recollect data in the background, which takes some time. You can click the Recollect button to manually collect the latest data, and after the data collection is complete, click Export Script to download it locally to confirm the index reclamation status.

    

  • Reorganize an index

    This method is suitable if the degree of fragmentation is low. However, the optimization effectiveness is not as good as that of the index rebuilding method.

Shrink data files

If the available storage is still insufficient after you have tried all suggested solutions, you can use one of the following methods to release data storage:

• Method 1: Shrink data files in batches. We recommend that you shrink 5 GB of data files at a time. Sample code:

  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   -- Size of data files that you shrink at a time (MB), we recommend 5GB at a time
  DECLARE @currentSize INT         -- Current size
  DECLARE @sql NVARCHAR(500)       
  DECLARE @waitTime INT = 10      -- Time to wait after each shrink operation in seconds
  
  -- Get data file name
  SELECT @fileName = name
  FROM sys.master_files
  WHERE database_id = DB_ID(@dbName)
  AND type_desc = 'ROWS'
  
  -- Loop shrinking
  WHILE 1 = 1
  BEGIN
      -- Get current file size
      SELECT @currentSize = size/128
      FROM sys.database_files
      WHERE name = @fileName
      
      -- Exit if the target size is reached
      IF @currentSize <= @targetSize
      BEGIN
          PRINT 'Shrink complete, current size: ' + CAST(@currentSize AS VARCHAR(20)) + 'MB'
          BREAK
      END
      
      -- Calculate the size after this shrink operation
      DECLARE @newSize INT = @currentSize - @shrinkSize
      IF @newSize < @targetSize
          SET @newSize = @targetSize
          
      -- Execute shrink operation
      SET @sql = 'DBCC SHRINKFILE (N''' + @fileName + ''', ' + CAST(@newSize AS VARCHAR(20)) + ')'
      PRINT 'Executing shrink: ' + @sql
      EXEC (@sql)
      
      -- Wait for a period of time before continuing
      PRINT 'Waiting ' + CAST(@waitTime AS VARCHAR(10)) + ' seconds before continuing...'
      WAITFOR DELAY '00:05:00'
  END

• Method 2: Run the DBCC SHRINKFILE command to shrink individual data files and release the unallocated data storage to the operating system. For more information, see Shrink a Database and DBCC SHRINKFILE (Transact-SQL).

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

  Expand to view sample code

  In the example shown above, the size of an extent is 64 KB. The total size of the data file is (1673344 x 64)/1024 = 104584 MB, and the allocated space is (1313432 x 64)/1024 = 82089.5 MB, which means that after compression, the total size of the data file cannot be less than 82089.5 MB. Therefore, to reduce the total size of this data file to 90000 MB, you can execute the following command:

  DBCC SHRINKFILE(1, 90000)

FAQ

View used log storage

Reclaiming log storage is relatively simple. You can use the DBCC SQLPERF(LOGSPACE) command or the autonomy service to view the percentage of used log storage in the database log files. If the percentage of used storage is high, shrinking log files will have almost no effect. You can query the system view sys.databases and check the output information in 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 Alerts page of your RDS instance to view the latest log storage usage.

Analysis

The temporary file storage is the amount of storage that is used by the tempdb system database. The tempdb system database uses only the SIMPLE recovery model. In normal cases, the total size of log files in the tempdb system database is small. However, the total size of data files in the tempdb system database can significantly increase within a short period of time. For example, if you create many temporary tables, join large tables, or sort data, the total size of data files in the tempdb system database increases.

Solutions

• Prevent storage consumption increases at the application level. For example, reduce unnecessary temporary tables, reduce queries that require the joins of large tables, and do not run large transactions.

• Restart your RDS instance during off-peak hours. After the restart, the amount of storage that is used by the tempdb system database decreases to the size at the time when the instance was created.

Analysis

Other file storage refers to the space occupied by sqlserver.other_size, mastersize, modelsize, msdbsize, and other similar files. In most cases, these files are small. However, these files can consume a large amount of storage space in the following scenarios:

• Many errorlog files exist, and the error log file size has grown to several GB or even larger.

• Memory dump files are generated when severe exceptions occur.

Solutions

1. You can go to the Monitoring And Alerts page of your RDS instance and click the Standard Monitoring tab to view the storage occupied by these files. For more information about the metrics, see View standard monitoring information.

   

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

   Note

   If other files (such as sqlserver.other_size) occupy too much space, please contact the helpdesk. They will help you identify the cause and resolve the issue.