Zoftcorp Logo
Back to All Blogs
SQL ServerBLOG POST

SQL Server – List all the database File Sizes and its free Spaces in an Instance – T-SQL Query from Zoftcorp

Feb 12, 2024
4 min read
By admin
SQL Server – List all the database File Sizes and its free Spaces in an Instance – T-SQL Query from Zoftcorp
ARTICLE SUMMARY

In this comprehensive guide, we provide a T-SQL query to list all database file sizes and their free spaces in an instance across all databases...

Overview: Monitoring Instance-Wide Database File Allocations

In this comprehensive guide, we provide a T-SQL query to list all database file sizes and their free spaces in an instance across all databases.

Managing storage capacity and file growth across multiple databases on a single SQL Server instance is one of the most critical operational tasks for DBAs. Running out of disk space on a primary data volume (MDF/NDF) or transaction log volume (LDF) can halt transaction processing and trigger database downtime. The script below iterates through all databases and outputs exact allocated size and available internal free space in Megabytes.

T-SQL Query: File Sizes and Free Space in an Instance

T-SQL Script // sys.databases & sys.database_files
sql
-- =============================================
-- Author:    Zoftcorp Consulting Services 
-- Create date: 27/10/2020 
-- Description:  To Find the database File Sizes and its Free spaces in an Instance 
-- ============================================= 
DECLARE @databases NVARCHAR(max) 
DECLARE @usedatabase NVARCHAR(max) 

CREATE TABLE #databasefilesizes 
  ( 
     [name]           NVARCHAR(max), 
     [filesize]       NUMERIC(38, 5), 
     [freespace]      NUMERIC(38, 5), 
     filetype         CHAR(10), 
     physicalfilename NVARCHAR(max) 
  ) 

DECLARE cur_eachdb CURSOR FOR 
  SELECT NAME 
  FROM   sys.databases 

OPEN cur_eachdb 

FETCH next FROM cur_eachdb INTO @databases 

WHILE @@FETCH_STATUS = 0 
  BEGIN 
      SET @usedatabase = 'USE [' + @databases 
                         + ']  insert into #DatabaseFileSizes  SELECT name, size/128.0 FileSizeInMB, size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int)/128.0     AS EmptySpaceInMB , type_desc,physical_name FROM sys.database_files; ' 

      PRINT @usedatabase 

      EXEC Sp_executesql 
        @usedatabase; 

      SET @usedatabase = '' 

      FETCH next FROM cur_eachdb INTO @databases 
  END 

CLOSE cur_eachdb; 

DEALLOCATE cur_eachdb; 

SELECT * 
FROM   #databasefilesizes;

How the T-SQL Script Operates:

TEMPORARY STORAGE

#databasefilesizes Table

Initializes a session-scoped temp table with precision columns for file size, free space, file type (ROWS/LOG), and physical OS paths.

ITERATION ENGINE

cur_eachdb Cursor

Loops through all online databases returned by sys.databases, ensuring an instance-wide inventory without manual per-database execution.

DYNAMIC CONTEXT

sp_executesql Context Switch

Dynamically issues USE [dbname] queries to interrogate each database’s local sys.database_files catalog view.

CAPACITY CONVERSION

FILEPROPERTY SpaceUsed

Converts 8-KB internal pages to Megabytes using size/128.0 and computes accurate EmptySpaceInMB by deducting active extents.

EXPERT CONSULTATION // ZOFTCORP SERVICES

Looking for a Reliable Database Partner?

If you are looking for a reliable and professional partner to help you migrate your database to the cloud, look no further than Zoftcorp. Contact us today to get a free consultation and quote for your database migration project at contact@zoftcorp.com.