ApsaraDB RDS:Check database and table storage in an RDS SQL Server instance

Check instance space

On the instance details page, view the total and used storage space in the basic information section.

In the usage statistics section, view the total and used storage space.

Check database size

1. Connect to the instance with an SSMS client. For more information, see Connect to an SQL Server instance.

2. To view the space usage of a single database, run the following SQL command:

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

   Parameter	Description
   database_size	The total size of the database, including data and log files.
   unallocated space	The unallocated space in the database.
   reserved	The total allocated space.
   data	The space used by data.
   index_size	The space used by indexes.
   unused	The allocated but unused space.

3. To view the space usage of all databases, run the following SQL command:

   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 following is a sample output:

   dbName    checkTime            dbSize       logSize
   master    2024-01-01 12:00:00  5.00 MB      1.50 MB
   tempdb    2024-01-01 12:00:00  8.00 MB      0.50 MB
   model     2024-01-01 12:00:00  3.25 MB      0.75 MB

   To view more detailed information about transaction log usage, such as the percentage of space used, run the following SQL command:

   DBCC SQLPERF(LOGSPACE);

   The result set returns the following columns: Database Name, Log Size (MB), Log Space Used (%), and Status.

Check table sizes in a database

1. Connect to the instance with an SSMS client. For more information, see Connect to an SQL Server instance.

2. To view the size of a single table in a database, run the following SQL command:

   USE <database_name>;
   GO
   EXEC sp_spaceused N'<table_name>';

   The following is a sample output:

   name    rows    reserved    data      index_size  unused
   zhtdb   10000   328 KB      304 KB    16 KB       8 KB

3. To view the sizes of all tables in a database, run the following SQL command:

   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;

   The following is a sample output:

   name    rows    reserved    data      index_size  unused
   zhtdb   10000   328 KB      304 KB    16 KB       8 KB

Related documents

• How to check the memory usage of an RDS SQL Server instance

• Storage space FAQ