:What do I do if the storage capacity of an ApsaraDB RDS for SQL Server instance is exhausted?

Problem description

The storage capacity of an RDS instance is exhausted.

Causes

High workloads exhaust the storage capacity of the RDS instance.

Solutions

In ApsaraDB RDS for SQL Server, the storage that is released after files are deleted is reused. If the storage usage is not high, you can choose not to reclaim storage by shrinking files. However, if the storage capacity is exhausted and your workloads are affected, you must reclaim storage. In most cases, we recommend that you preferentially reclaim storage by shrinking log files. This is because log files can be shrunk within a short period of time. As a result, sufficient storage can be released within a short period of time. To shrink a log file, perform the following steps:

1. Check the size of the log file that you want to shrink.

   • If your RDS instance runs SQL Server 2012 or SQL Server 2016, execute the following SQL statement to check the size of the log file:

     SELECT DB_NAME(database_id) AS [Database Name],
            [Name] AS [Logical Name],
            [Physical_Name] AS [Physical Name],
            ((size * 8) / 1024) AS [Size(MB)]
     FROM sys.master_files
     ORDER BY [Size(MB)] DESC

   • If your RDS instance runs SQL Server 2008 R2, execute the following SQL statement to check the size of the log file:

     Note

     You must execute the SQL statement on all databases one by one.

     USE [$Database_Name] GO
     SELECT a.name AS [$Logic_Name], size/128,
                                  FILEPROPERTY(a.name, 'SpaceUsed')/128,
                                  size/128 - FILEPROPERTY(a.name, 'SpaceUsed')/128,
                                  FILEPROPERTY(a.name, 'SpaceUsed')*100.0/size
     FROM sys.database_files a
     cross join
       (select recovery_model_desc,
               log_reuse_wait,
               log_reuse_wait_desc,
               is_auto_shrink_on
        from sys.databases
        where name=DB_NAME())b
     WHERE type=1

     Note

     • [$Database_Name] specifies the name of the database.

     • [$Logic_Name] specifies the name of the logical file.

2. Execute the following SQL statement to check whether the log file can be shrunk:

   SELECT [name],
          [log_reuse_wait_desc]
   FROM master.sys.databases
   WHERE [name]='[$Database_Name]'

3. Execute the following SQL statement to shrink the log file to reclaim storage:

   DBCC SHRINKFILE([$Logic_Name])

   Note

   • We recommend that you execute the SQL statement during off-peak hours to prevent service interruptions.

   • For more information about the log wait types and solutions, see More information.

4. Execute the following SQL statement to view the size of data files:

   USE [$Database_Name] GO
   SELECT a.name AS [$Logic_Name],
          size/128,
               FILEPROPERTY(a.name, 'SpaceUsed')/128,
               size/128 - FILEPROPERTY(a.name, 'SpaceUsed')/128,
                    FILEPROPERTY(a.name, 'SpaceUsed')*100.0/size
   FROM sys.database_files a
   cross join
     (select recovery_model_desc,
             log_reuse_wait,
             log_reuse_wait_desc,
             is_auto_shrink_on
      from sys.databases
      where name=DB_NAME())b
   WHERE type=0

5. Execute the following SQL statement to shrink the data files:

   Note

   In most cases, files are shrunk at a step size of 5 GB. If the operation affects your workloads, you can stop the operation. However, the operation is not rolled back.

   DECLARE @usedspace INT, @totalspace INT
   SELECT @usedspace = xxx,
          @totalspace = yyy
   WHILE @totalspace > @usedspace
   BEGIN
       SET @totalspace = @totalspace - 5 * 1024
       DBCC SHRINKFILE([$Logic_Name], @totalspace)
   END

   Note

   You can obtain the information about the used storage and storage capacity from the previous step.

6. Execute the following SQL statement to view the shrinking progress:

   Note

   The progress is an estimated value.

   SELECT DB_NAME(database_id) AS dbname,
          session_id,
          request_id,
          start_time,
          percent_complete,
          dateadd(mi, estimated_completion_time/60000, getdate()) AS ETC
   FROM sys.dm_exec_requests
   WHERE percent_complete <> 0

More information

This section describes the common log wait types and solutions.

1. LOG_BACKUP

   The log files are not backed up and cannot be shrunk.

   Log on to the ApsaraDB RDS console. In the left-side navigation pane of the instance details page, click Backup and Restoration. On the page that appears, click the Backup Settings tab. Then, configure the parameters and click Shrink Transaction Log. For more information about the parameter settings, see Back up an ApsaraDB RDS for SQL Server instance.

2. ACTIVE_TRANSACTION

   Active transactions block log file shrinking. In this case, perform the following operations:

   1. Execute the following SQL statement to obtain the SPID of the transaction that is active for a long period of time:

      DBCC OPENTRAN

   2. Replace [$SPID] in the following SQL statement with the SPID that is obtained from the previous step and execute the SQL statement to view the requested SQL statement and check whether the active transaction can be terminated by using the kill command. After the active transaction is terminated, view the value of log_reuse_wait and shrink the file.

      DBCC INPUTBUFFER([$SPID])

References

How do I shrink the transaction logs of an ApsaraDB RDS for SQL Server instance?

Application scope

ApsaraDB RDS for SQL Server