Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
SQL database in Microsoft Fabric
This article describes how to display the data and log space information for a database in SQL Server by using SQL Server Management Studio or Transact-SQL.
Permission to run sp_spaceused is granted to the public role. Only members of the db_owner fixed database role can specify the @updateusage parameter.
In Object Explorer, connect to an instance of SQL Server and then expand that instance.
Expand Databases.
Right-click a database, point to Reports, point to Standard Reports, and then select Disk Usage.
Connect to the Database Engine.
On the Standard toolbar, select New Query.
Paste the following example into the query window and then select Execute. This example uses the sp_spaceused system stored procedure to report disk space information for the entire database, including tables and indexes.
USE AdventureWorks2022;
GO
EXEC sp_spaceused;
GO
Connect to the Database Engine.
On the Standard toolbar, select New Query.
Paste the following example into the query window and then select Execute. This example queries object catalog views to report disk space usage per table and within each table per allocation unit.
SELECT
t.object_id,
OBJECT_NAME(t.object_id) ObjectName,
sum(u.total_pages) * 8 Total_Reserved_kb,
sum(u.used_pages) * 8 Used_Space_kb,
u.type_desc,
max(p.rows) RowsCount
FROM
sys.allocation_units u
JOIN sys.partitions p on u.container_id = p.hobt_id
JOIN sys.tables t on p.object_id = t.object_id
GROUP BY
t.object_id,
OBJECT_NAME(t.object_id),
u.type_desc
ORDER BY
Used_Space_kb desc,
ObjectName;
Connect to the Database Engine.
On the Standard toolbar, select New Query.
Paste the following example into the query window then select Execute. This example queries the sys.database_files catalog view to return specific information about the data and log files in the AdventureWorks2022
database.
USE AdventureWorks2022;
GO
SELECT file_id, name, type_desc, physical_name, size, max_size
FROM sys.database_files;
GO
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayTraining
Module
Configure SQL Server resources for optimal performance - Training
Configure SQL Server resources for optimal performance
Certification
Microsoft Certified: Azure Database Administrator Associate - Certifications
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.
Documentation
sp_spaceused (Transact-SQL) - SQL Server
sp_spaceused displays the number of rows, disk space reserved, and disk space used by objects in the database.
Shrink a database - SQL Server
Learn how to shrink a database in SQL Server by using SQL Server Management Studio or Transact-SQL.
DBCC SHRINKFILE (Transact-SQL) - SQL Server
DBCC SHRINKFILE shrinks the size of a database file.