Share via


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 2012 by using SQL Server Management Studio or Transact-SQL.

In This Topic

  • Before you begin:

    Security

  • To display data and log space information for a database, using:

    SQL Server Management Studio

    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.

Arrow icon used with Back to Top link[Top]

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.

Arrow icon used with Back to Top link[Top]

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

Arrow icon used with Back to Top link[Top]

See Also

Reference

SELECT (Transact-SQL)

sys.database_files (Transact-SQL)

sp_spaceused (Transact-SQL)

Concepts

Add Data or Log Files to a Database

Delete Data or Log Files from a Database