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

View storage usage

• Log on to the ApsaraDB RDS console and go to the Basic Information page. In the Usage Statistics section of the page, view the overall storage usage of your RDS instance. The Usage Statistics section does not provide the current or historical storage usage for various data types.

• Log on to the ApsaraDB RDS console and go to the Monitoring and Alerts page. On the Legacy Monitoring tab of the page, click Resource Monitoring. Then, view the current and historical storage usage for various data types.

  Note

  The Other System File Size metric indicates the amount of storage space that is used by some system files and all the data files and log files in the master, msdb, and model system databases. These system files include error log files, default trace files, and system extended event files.

  

• Log on to the ApsaraDB RDS console and choose Autonomy Service > Storage Management. Then, view the storage usage of your RDS instance. The storage usage includes the percentages of used data storage space and used log storage space, the storage consumption trends, and the storage consumption for each of the top 10 databases and top 20 data tables that consume the largest storage space. For more information, see View the storage information of an ApsaraDB RDS for SQL Server instance.

  Note

  Your RDS instance does not run SQL Server 2008 R2 with cloud disks.

  

• Use a client tool, such as SQL Server Management Studio (SSMS), to view the storage usage of your RDS instance.

  The following table describes the system views and commands that can be used to query the storage usage of your RDS instance.

  System view or command	Description
  sp_helpdb	Used to query the total storage space of each database. The total storage space of a database is equal to the total size of data files and log files in the database.
  sp_spaceused	Used to query the name, used storage space, and unallocated storage space of the database to which you are logged on.
  DBCC SQLPERF(LOGSPACE)	Used to query the total log storage space and used log storage space of each database.
  DBCC SHOWFILESTATS	Used to query the total data storage space and used data storage space of the database to which you are logged on.
  select * from sys.master_files	Used to query the total size of data files and the total size of log files in each database.
  select * from sys.dm_db_log_space_usage	Used to query the total log storage space and used log storage space of the database to which you are logged on. Note This command is supported only when your RDS instance runs SQL Server 2012 or later.
  select * from sys.dm_db_file_space_usage	Used to query the total data storage space and used data storage space of the database to which you are logged on. Note This command is supported only when your RDS instance runs SQL Server 2012 or later.

If the storage usage of your RDS instance is abnormally high, log on to the ApsaraDB RDS console and go to the Monitoring and Alerts page. View the storage usage for data files, log files, temporary files, and system files to identify the type of file that consumes an abnormally increased amount of storage space. Then, evaluate whether you can use the suggested solutions to release storage space or prevent an abnormal increase in storage consumption.

For more information about the storage analysis and suggested solutions, see the following sections.

Reclaim data storage space

• Analysis

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

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

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

• Solution

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

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

  • 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. In addition, this method 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 version earlier than 2016 on RDS Enterprise Edition, 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 space that you can save by enabling the data compression feature 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 may be locked for a long period of time. The locking of the tables may interrupt your workloads. We recommend that you modify the compression option settings during off-peak hours.

    • If your RDS instance runs SQL Server on RDS Enterprise Edition, 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. Therefore, 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 space. In this case, you can defragment the index to reduce the amount of used data storage space.

    Log on to the ApsaraDB RDS console and choose Autonomy Service . 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 SQL Server on RDS Enterprise Edition, 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 effect is not as good as the optimization effect 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 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 space 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

    ApsaraDB RDS needs to read 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 space.

If the available storage space is still insufficient after you have tried all the suggested solutions, you can run the DBCC SHRINKFILE command to compress data files. This way, the unallocated data storage space is released to the operating system.

In the preceding figure, the size per extent is 64 KB. Therefore, the total data storage space is 104,584 MB, and the allocated data storage space is 82,089 MB. In this case, the total size of the compressed data files is greater than or equal to 82,089 MB. If you want to reduce the total data storage space to 90,000 MB, run the following command:

DBCC SHRINKFILE(1, 90000)

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

Reclaim log storage space

Run the DBCC SQLPERF(LOGSPACE) command or use the autonomy service to view the percentage of used log storage space. If the percentage is high, the amount of storage space that you can release by compressing 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 space.

In most cases, you do not need to manually compress log files. ApsaraDB RDS compresses log files every time when an automatic backup is complete. If you need to reduce the amount of used log storage space by compressing log files at the earliest opportunity, you can perform the following steps: Log on to the ApsaraDB RDS console and go to the Backup and Restoration page. In the upper part of the page, click Shrink Transaction Log. Then, ApsaraDB RDS starts to back up all transaction logs and compress log files. For example, if the available storage space of your RDS instance is abnormally low due to an increase in log storage consumption and you cannot wait until the next automatic backup, you can perform these steps.

Reclaim temporary file storage space

• Analysis

  The temporary file storage space is the amount of storage space 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.

• Solution

  • 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 space that is used by the tempdb system database decreases to the size at the time when the instance was created.

Reclaim system file storage space

• Analysis

  The system file storage space is the total amount of storage space that is used by the files in the master, msdb, and model system databases and some files in the system directories. In most cases, these files are small. However, these files can consume a large amount of storage space 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.

• Solution

  You cannot obtain the storage space that is used by various system files.

Expand storage capacity

If the storage usage of your RDS instance is still abnormally high after you have tried all the suggested solutions, you can expand the storage capacity of the instance. For more information, see Change the specifications of an ApsaraDB RDS for SQL Server instance.