display-data-and-log-space-information-for-a-database.md

title	Display data & log space info for a database
description	Learn how to display the data and log space information for a database in SQL Server by using SQL Server Management Studio or Transact-SQL.
author	WilliamDAssafMSFT
ms.author	wiassaf
ms.date	06/16/2021
ms.service	sql
ms.subservice	supportability
ms.topic	conceptual
helpviewer_keywords	logs [SQL Server], space status information [SQL Server], space displaying space information disk space [SQL Server], displaying databases [SQL Server], space used viewing space information space allocation [SQL Server], displaying data space [SQL Server]
monikerRange	>=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

Display data and log space information for a database

[!INCLUDESQL Server Azure SQL Database Synapse Analytics PDW] This article describes how to display the data and log space information for a database in [!INCLUDE ssnoversion-md] by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql].

Before you begin

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.

Using SQL Server Management Studio

To display data and log space information for a database

1. In Object Explorer, connect to an instance of [!INCLUDEssNoVersion] and then expand that instance.

2. Expand Databases.

3. Right-click a database, point to Reports, point to Standard Reports, and then select Disk Usage.

Using Transact-SQL

To display data and log space information for a database by using sp_spaceused

1. Connect to the [!INCLUDEssDE].

2. On the Standard toolbar, select New Query.

3. 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  

To display data space used, by object and allocation unit, for a database

1. Connect to the [!INCLUDEssDE].

2. On the Standard toolbar, select New Query.

3. 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;
   

To display data and log space information for a database by querying sys.database_files

1. Connect to the [!INCLUDEssDE].

2. On the Standard toolbar, select New Query.

3. 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 [!INCLUDEssSampleDBobject] database.

   USE AdventureWorks2022;  
   GO  
   SELECT file_id, name, type_desc, physical_name, size, max_size  
   FROM sys.database_files;  
   
   GO  
   

See also

SELECT (Transact-SQL)
sys.database_files (Transact-SQL)
sp_spaceused (Transact-SQL)
Add data or log files to a database
Delete data or log files from a database