Reclaim tablespace for an ApsaraDB RDS for SQL Server instance

Problem description

In ApsaraDB RDS for SQL Server, the size of a table does not automatically decrease when you delete or shorten variable-length columns. These columns include field types such as varchar, nvarchar, varchar(max), nvarchar(max), varbinary, text, ntext, image, sql_variant, varbinary(max), and xml.

Cause

This issue occurs because space is not reclaimed automatically. After you delete data, the space that the records occupied remains allocated. When you insert a new record, the database prioritizes using these empty slots.

Solution

You can reclaim space by rebuilding the clustered index periodically. However, this action does not reduce the size of the database's data file. To shrink a data file in SQL Server, you can use the DBCC SHRINKDATABASE command to shrink all data and log files in a database, or the DBCC SHRINKFILE command to shrink a specific data or log file.

In MySQL, each table typically has its own file. Therefore, if you shrink a large table, the overall database size decreases. In contrast, all tables in SQL Server are stored within the database's files. Therefore, you can only reclaim disk space by shrinking these files. This topic describes two methods:

• Method 1: Use the DBCC CLEANTABLE command to reclaim space from dropped variable-length columns in tables or indexed views. 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 following are the descriptions:

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

  The following example shows how to use the command:

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

• Method 2: Rebuild the index .