
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
How the T-SQL Script Operates:
#databasefilesizes Table
Initializes a session-scoped temp table with precision columns for file size, free space, file type (ROWS/LOG), and physical OS paths.
cur_eachdb Cursor
Loops through all online databases returned by sys.databases, ensuring an instance-wide inventory without manual per-database execution.
sp_executesql Context Switch
Dynamically issues USE [dbname] queries to interrogate each database’s local sys.database_files catalog view.
FILEPROPERTY SpaceUsed
Converts 8-KB internal pages to Megabytes using size/128.0 and computes accurate EmptySpaceInMB by deducting active extents.
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.