Skip to content

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

Latest commit

 

History

History
88 lines (64 loc) · 4.2 KB

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

File metadata and controls

88 lines (64 loc) · 4.2 KB
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, sql-data-warehouse, 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 stevestein
ms.author sstein
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 Vendor table and its indexes.

USE AdventureWorks2012;  
GO  
EXEC sp_spaceused N'Purchasing.Vendor';  
GO

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