Maintaining visibility of database file sizes and available space in SQL Server is essential for Database Administrators (DBAs) to effectively manage databases. Monitoring the availability of free space in both data and log files is crucial for ensuring the longevity and performance of a database environment. In this blog post, we’ll share a SQL script that will get all database file sizes and free space within, which empowers DBAs to make informed decisions regarding future disk space requirements.

The Importance of Monitoring Database File Sizes

Understanding the growth patterns of databases is essential for planning and allocating resources effectively. By monitoring database file sizes, DBAs can anticipate future disk space needs and proactively address potential capacity constraints. Additionally, tracking free space within databases enables administrators to identify and address any anomalies or inefficiencies that may impact database performance.

SQL Script to Show Free Space in Databases

The SQL script below retrieves information for all database files in SQL Server, including file sizes and free space within the database files.

-- Get sizes of all database and log files, store into a temp table & select output
DROP TABLE IF EXISTS #MSSQL_Database_Sizes
CREATE TABLE #MSSQL_Database_Sizes (
    [database_name] [nvarchar](MAX) NULL, 
    current_size_mb [int] NULL, 
    freespace_mb [int] NULL, 
    collection_date [date] NULL
)

DECLARE @db_name NVARCHAR(MAX)

DECLARE db_cursor CURSOR FOR  
SELECT name
FROM sys.databases
-- WHERE database_id > 4 -- Exclude system databases

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @db_name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    DECLARE @command NVARCHAR(MAX)
    SET @command =       
        'USE ' + QUOTENAME(@db_name) + '
        INSERT INTO #MSSQL_Database_Sizes ([database_name], current_size_mb, freespace_mb, [collection_date]) 
        SELECT 
            [name] AS FileName,
            [size]/128.0 AS [current_size_mb], 
            [size]/128.0 - CAST(FILEPROPERTY([name], ''SpaceUsed'') AS INT)/128.0 AS [freespace_mb],
            CAST(GETDATE() AS DATE) AS [collection_date]
        FROM sys.database_files'

    EXEC sp_executesql @command

    FETCH NEXT FROM db_cursor INTO @db_name
END  

CLOSE db_cursor  
DEALLOCATE db_cursor  

SELECT *
FROM #MSSQL_Database_Sizes
ORDER BY 2 DESC

The script outputs info for all databases on the SQL Server instance, providing insights into the free space within each database. In the example, ample free space is observed, suggesting minimal data in the databases. The variation in free space within log files depends on the Recovery Model in use.

Understanding how databases grow, particularly during Autogrowth events, aids in predicting future disk space needs. This information is invaluable for maintaining an efficient and responsive database environment.

While it’s possible to shrink free space within databases to reduce file sizes on disk, caution is advised, especially in production environments. Future space requirements may arise, necessitating extra room for seamless database operations.