Display Data and Log Space Information for a Database
In this tutorial we will learn how to display the data and log space information for a database in SQL Server 2019 (15.x) by using SQL Server Management Studio or Transact-SQL.
Before You Begin
The public role is given permission to run sp_spaceused. The @updateusage argument is only available to members of the db_owner fixed database role.
Using SQL Server Management Studio
To show information about a database’s data and log space.
- Firstly, in Object Explorer, connect to an instance of SQL Server and then expand that instance.
- Secondly, expand Databases.
- Lastly, right-click a database, point to Reports, point to Standard Reports,, and then click Disk Usage.
Using Transact-SQL
Using sp_spaceused, you may show data and log space information for a database.
- Firstly, connect to the Database Engine.
- Secondly, from the Standard bar, click New Query.
- Lastly, 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.
SQL
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
- Firstly, connect to the Database Engine.
- Secondly, from the Standard bar, click New Query.
- Lastly, 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.
SQL
USE AdventureWorks2012;
GO
SELECT file_id, name, type_desc, physical_name, size, max_size
FROM sys.database_files ;
GO
Reference: Microsoft Documentation