ApsaraDB RDS:How do I view the storage capacity used by ApsaraDB RDS for SQL Server databases and tables in the databases?

View the storage capacity of an RDS for SQL Server instance

You can go to the instance detail page of the RDS for SQL Server instance and view the storage capacity of the instance on the Basic Information page.

View the storage capacity used by databases

1. Use the SSMS client to connect to the RDS for SQL Server instance. For more information, see Connect to an ApsaraDB RDS for SQL Server instance.

2. Run the following commands to view the storage capacity used by a database:

   USE <Database name>;
   GO
   EXEC sp_spaceused @updateusage = N'TRUE';

   Parameter	Description
   database_size	The size of the database, including the sizes of data files and log files. The value is greater than the sum of the values of the reserved and unallocated space parameters.
   unallocated space	The size of unallocated storage of the database.
   reserved	The total size of storage allocated for the database.
   data	The storage that is occupied by data.
   index_size	The storage that is occupied by indexes.
   unused	The storage that is allocated but is unused.

3. Run the following commands to view the storage capacity used by all databases in the instance:

   USE master
   GO
   DECLARE @insSize TABLE(dbName sysname,checkTime VARCHAR(19),dbSize VARCHAR(50),logSize VARCHAR(50))
   INSERT INTO @insSize ( dbName, checkTime, dbSize, logSize )
   EXEC sp_msforeachdb 'select ''?'' dbName,CONVERT(VARCHAR(19),GETDATE(),120) checkTime,LTRIM(STR(SUM(CASE WHEN RIGHT(FILENAME,3)<>''ldf'' THEN convert (dec (15,2),size) * 8 / 1024 ELSE 0 END),15,2)+'' MB'') dbSize,  
                    LTRIM(STR(SUM(CASE WHEN RIGHT(FILENAME,3)=''ldf''  THEN convert (dec (15,2),size) * 8 / 1024 ELSE 0 END),15,2)+'' MB'') logSize from ?.dbo.sysfiles'
   SELECT * FROM @insSize ORDER BY CONVERT(DECIMAL,LTRIM(RTRIM(SUBSTRING(dbSize,1,LEN(dbSize)-2)))) DESC

   The result does not include the storage capacity used by log files. You can execute the following statement to view the size of log files:

   DBCC SQLPERF(LOGSPACE);

View the storage capacity used by tables in a database

1. Use the SSMS client to connect to the RDS for SQL Server instance. For more information, see Connect to an ApsaraDB RDS for SQL Server instance.

2. Run the following commands to view the storage capacity used by a table in a database.

   USE <Database name>;
   GO
   EXEC sp_spaceused N'<Table name>';

3. Run the following commands to view the storage capacity used by all tables in the database:

   USE <Database name>;
   GO
   DECLARE @tabSize TABLE (
   name NVARCHAR(100),
   rows CHAR(20),
   reserved VARCHAR(18),
   data VARCHAR(18),
   index_size VARCHAR(18),
   unused VARCHAR(18)
   );
   
   INSERT INTO @tabSize 
   EXEC sp_MSForEachTable '
   EXEC sp_spaceused ''?''';
   
   SELECT * 
   FROM @tabSize 
   ORDER BY CONVERT(INT, REPLACE([data], 'KB', '')) DESC, 2 DESC;

References

• How do I view the memory usage of an ApsaraDB RDS for SQL Server instance?

• FAQ about storage capacity