How to reclaim tablespace in RDS for SQL Server

Problem description

In RDS for SQL Server, the size of a table does not automatically decrease after you delete variable-length columns or reduce their length. Variable-length columns include data types such as varchar, nvarchar, varchar(max), nvarchar(max), varbinary, text, ntext, image, sql_variant, varbinary(max), and xml.

Cause

This happens because space is not automatically reclaimed. When you delete data, the space that the data occupied is not deallocated. When you insert a new record, the database prioritizes this empty space.

Solutions

You can periodically rebuild the clustered index. Note that even if you reclaim space from a table, the size of the database's data file does not decrease. To shrink a data file in SQL Server, you can use the DBCC SHRINKDATABASE command to shrink the data and log files for a specified database. Alternatively, you can use the DBCC SHRINKFILE command to shrink a specific data or log file for the current database.

In MySQL, each table is typically stored in a separate file. Therefore, if you reclaim space from a large table, the overall database size decreases. In contrast, all tables in SQL Server are stored in shared database files. To reduce the overall size, you must shrink these files. This topic describes the following two methods:

• Method 1: Use the DBCC CLEANTABLE command to reclaim space from deleted variable-length columns in a table or indexed view. The syntax is as follows:

  DBCC CLEANTABLE  
  (  
      { database_name | database_id | 0 }  
      , { table_name | table_id | view_name | view_id }  
      [ , batch_size ]  
  )  
  [ WITH NO_INFOMSGS ]

  The parameters are described as follows:

  Statement	Description
  database_name | database_id | 0	The database that contains the table to be purged. If you specify 0, the current database is used.
  table_name | table_id | view_name | view_id	The table or indexed view to be purged.
  batch_size	The number of rows processed per transaction. If this is not specified or is set to 0, the statement processes the entire table in one transaction by default.
  WITH NO_INFOMSGS	Suppresses all informational messages.

  The following is an example:

  DBCC CLEANTABLE (testDB,'testTable', 0)  
  WITH NO_INFOMSGS;  
  GO

• Method 2: Rebuild the index .