Skip to content

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

Latest commit

 

History

History
95 lines (71 loc) · 4.17 KB

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

File metadata and controls

95 lines (71 loc) · 4.17 KB

title: "Display Data and Log Space Information for a Database | Microsoft Docs" ms.custom: "" ms.date: "08/01/2016" ms.prod: "sql" ms.prod_service: "database-engine, sql-database, sql-data-warehouse, pdw" ms.service: "" ms.component: "databases" ms.reviewer: "" ms.suite: "sql" ms.technology:

  • "database-engine" ms.tgt_pltfrm: "" ms.topic: "article" 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 caps.latest.revision: 28 author: "stevestein" ms.author: "sstein" manager: "craigg" ms.workload: "Active" monikerRange: ">= aps-pdw-2016 || = azuresqldb-current || = azure-sqldw-latest || >= sql-server-2016 || = sqlallproducts-allversions"

Display Data and Log Space Information for a Database

[!INCLUDEappliesto-ss-asdb-asdw-pdw-md] 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