How to find free data and log space in SQL Server Database

In this article, we will see how to find free data and log space in SQL Server Database using different methods.

Find free data and log space in SQL Server Database using SQL Query:

 
use database_name
SELECT RTRIM(name) AS [Segment Name], groupid AS [Group Id], filename AS [File Name],
   CAST(size/128.0 AS DECIMAL(10,2)) AS [Allocated Size in MB],
   CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2)) AS [Space Used in MB],
   CAST([maxsize]/128.0 AS DECIMAL(10,2)) AS [Max in MB],
   CAST(size/128.0-(FILEPROPERTY(name, 'SpaceUsed')/128.0) AS DECIMAL(10,2)) AS [Available Space in MB],
   CAST((CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST(size/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) AS [Percent Used]
FROM sysfiles
ORDER BY groupid DESC

Output:

Find free data and log space in SQL Server Database using Stored Procedure:

USE training;  
GO  
sp_spaceused

Find free log space in SQL Server Database using DBCC command:
Following command provides transaction log space usage statistics of entire database of the SQL Server. LOGSPACE gives the current size of the transaction log and the percentage of log space used for every database.

USE training;  
GO  
DBCC SQLPERF(logspace)
go

Find free log space in SQL Server Database using SSMS:

1. Expand Databases.
2. Right-click a database, point to Reports, point to Standard Reports,, and then click Disk Usage as shown below.

3. Report provide following information.

Find free log space in SQL Server Database querying sys.database_files:

SELECT file_id, name, type_desc, physical_name, size, max_size  
FROM sys.database_files ;  
GO  

Output: