ApsaraDB RDS:Troubleshoot insufficient storage issues on an ApsaraDB RDS for SQL Server instance

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 the 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 is effective to mitigate the 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 EE earlier than SQL Server 2016 EE, the data compression feature is provided. This feature supports row compression and page compression. You can enable this feature on individual tables, indexes, or extents. 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. This dedicated stored procedure is used to evaluate the amount of data storage that you can save by enabling the data compression feature on a specific 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.

  • If your RDS instance runs an SQL Server EE, you can set the ONLINE parameter to ON. Then, you can run DDL operations to modify the compression option settings. These DDL operations do not interrupt your workloads.

  • 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.

  Log on to the ApsaraDB RDS console and choose Autonomy Services. 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.

  • Rebuild an index

    This method is suitable if the degree of fragmentation is high. By default, when you rebuild an index, the table on which the index is created is locked during the rebuild process. If your RDS instance runs an SQL Server EE, you can set the ONLINE parameter to ON. This helps prevent a long-term lock on the table.

  • 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.

  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 in index pages. In normal cases, an index with a high degree of fragmentation is likely to be defragmented to reclaim storage.

  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).

  Note

  The system reads a large number of 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.

  Index defragmentation is suitable only for archived data tables that are updated at a low frequency. If frequent insert and update operations are performed on a data table, the degrees of fragmentation in the indexes on the data table significantly increase. In addition, if you rebuild or reorganize the indexes on the data table, a large number of transaction logs are generated. This increases the amount of used log storage.

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)  -- Name of the data file.
  DECLARE @targetSize INT=1024 -- Upper limit of the file size in MB.
  DECLARE @shrinkSize INT = 5120   -- Size of data files that you shrink at a time. Unit: MB. We recommend that you shrink 5 GB of data files at a time.
  DECLARE @currentSize INT         -- Current file size.
  DECLARE @sql NVARCHAR(500)       
  DECLARE @waitTime INT = 10      -- Time to wait after each shrink operation in seconds.
  
  -- Obtain the name of the data file.
  SELECT @fileName = name
  FROM sys.master_files
  WHERE database_id = DB_ID(@dbName)
  AND type_desc = 'ROWS'
  
  -- Shrink data files in batches.
  WHILE 1 = 1
  BEGIN
      -- Query the size of the current file.
      SELECT @currentSize = size/128
      FROM sys.database_files
      WHERE name = @fileName
      
      -- Exit if the upper limit of the file size is reached.
      IF @currentSize <= @targetSize
      BEGIN
          PRINT 'Shrink complete. Current file size: ' + CAST(@currentSize AS VARCHAR(20)) + 'MB'
          BREAK
      END
      
      -- Calculate the size of remaining files after the shrink operation.
      DECLARE @newSize INT = @currentSize - @shrinkSize
      IF @newSize < @targetSize
          SET @newSize = @targetSize
          
      -- Shrink data files.
      SET @sql = 'DBCC SHRINKFILE (N''' + @fileName + ''', ' + CAST(@newSize AS VARCHAR(20)) + ')'
      PRINT 'Perform the shrink operation: ' + @sql
      EXEC (@sql)
      
      - Wait a period of time and continue to shrink.
      PRINT 'Wait ' + CAST(@waitTime AS VARCHAR(10)) + ' seconds and continue to shrink...'
      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 preceding figure, the size per extent is 64 KB. Therefore, the total data storage is 104,584 MB, and the allocated data storage is 82,089.5 MB. In this case, the total size of the compressed data files is greater than or equal to 82,089.5 MB. The total data storage is obtained by using the following calculation: (1673344 × 64)/1024. The allocated data storage is obtained by using the following calculation: (1313432 × 64)/1024. If you want to reduce the total storage of data files to 90,000 MB, you can run the following command:

  DBCC SHRINKFILE(1, 90000)

View used log storage

Run the DBCC SQLPERF(LOGSPACE) command or use the autonomy service to view the percentage of used log storage. If the percentage is high, the amount of storage that you can release by shrinking log files is small. In this case, you can query the sys.databases system view. Then, you can view the values in the log_reuse_wait and log_reuse_wait_desc columns of the returned result. The values provide more information about why you cannot reclaim log storage.

Shrink transaction logs

After the log files are shrunk, 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 a large number of 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 indicates the storage that is occupied by sqlserver.other_size, mastersize, modelsize, and msdbsize. In most cases, these files are small. However, these files can consume a large amount of storage in the following scenarios:

• A large number of error logs are generated. In this case, the total size of error log files increases to a few GB or more.

• Memory dump files are generated when severe exceptions occur.

Solutions

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

   

2. If error logs occupy a large amount of storage, clear the error logs on the Logs page. For more information, see Manage logs.

   Note

   If other files, such as sqlserver.other_size, occupy a large amount of storage, contact technical support to identify the cause and resolve the issue.