Display Data and Log Space Information for a Database

This topic describes how to display the data and log space information for a database in SQL Server 2016 by using SQL Server Management Studio or Transact-SQL.

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 SQL Server 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 Database Engine.

  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 Database Engine.

  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 AdventureWorks2012 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