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.
ms.date	08/01/2016
ms.prod	sql
ms.prod_service	database-engine, sql-database, synapse-analytics, pdw
ms.reviewer
ms.technology	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]
ms.assetid	c7b99463-4bab-4e9b-9217-fcb0898dc757
author	WilliamDAssafMSFT
ms.author	wiassaf
monikerRange	>=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current
ms.custom	seo-lt-2019

Display Data and Log Space Information for a Database

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

Before You Begin

Security

Permissions

Permission to execute 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 click 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. From the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute. This example uses the sp_spaceused system stored procedure to report disk space information for the entire database - tables and indexes.

USE AdventureWorks2012;  
GO  
EXEC sp_spaceused;  
GO  

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

1. Connect to the [!INCLUDEssDE].

2. From the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click 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. From the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click 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 AdventureWorks2012;  
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