Skip to content

Latest commit

 

History

History
113 lines (84 loc) · 5.24 KB

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

File metadata and controls

113 lines (84 loc) · 5.24 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic ms.custom helpviewer_keywords monikerRange
Display data & log space info for a database
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.
WilliamDAssafMSFT
wiassaf
07/19/2024
sql
supportability
how-to
ignite-2024
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]
>=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric

Display data and log space information for a database

[!INCLUDE SQL Server Azure SQL Database Synapse Analytics PDW FabricSQLDB]

This article describes how to display the data and log space information for a database in [!INCLUDE ssnoversion-md] by using [!INCLUDE ssManStudioFull] or [!INCLUDE tsql].

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.

Use SQL Server Management Studio

Display data and log space information for a database

  1. In Object Explorer, connect to an instance of [!INCLUDE ssNoVersion] 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.

Use Transact-SQL

Display data and log space information for a database by using sp_spaceused

  1. Connect to the [!INCLUDE ssDE].

  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

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

  1. Connect to the [!INCLUDE ssDE].

  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;

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

  1. Connect to the [!INCLUDE ssDE].

  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 [!INCLUDE ssSampleDBobject] database.

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

Related content