Use the console to check total instance storage, and run T-SQL queries to drill down into per-database and per-table usage.
View instance storage capacity
On the instance details page, go to the Basic Information tab. The page shows the total provisioned storage and current usage.
View storage used by databases
Prerequisites
Before you begin, ensure that you have:
Connected to the RDS for SQL Server instance using SQL Server Management Studio (SSMS). For instructions, see Connect to an ApsaraDB RDS for SQL Server instance.
View a single database
Run the following query to check storage for a specific database:
USE <Database name>;
GO
EXEC sp_spaceused @updateusage = N'TRUE';The query returns two result sets. The table below describes all returned columns:
| Column | Description |
|---|---|
database_size | Total size of the database, including data files and log files. This value is greater than the sum of reserved and unallocated space because log files are not counted in those two columns. |
unallocated space | Storage in the database that has not been reserved for any database object. |
reserved | Total storage allocated to database objects. |
data | Storage used by data. |
index_size | Storage used by indexes. |
unused | Storage allocated to objects but not yet used. |
View all databases in the instance
Run the following query to list storage usage for every database, sorted by data size in descending order:
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)))) DESCThe result does not include the storage capacity used by log files. To view log space usage separately, run DBCC SQLPERF(LOGSPACE);.
View storage used by tables
Prerequisites
Before you begin, ensure that you have:
Connected to the RDS for SQL Server instance using SQL Server Management Studio (SSMS). For instructions, see Connect to an ApsaraDB RDS for SQL Server instance.
View a single table
Run the following query to check storage for a specific table:
USE <Database name>;
GO
EXEC sp_spaceused N'<Table name>';View all tables in a database
Run the following query to list storage usage for every table in a database, sorted by data size in descending order:
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;