SQL Server Script: Get Database Sizes and Free Space

Effectively managing database file sizes and monitoring free space within data and log files is crucial for maintaining optimal performance in SQL Server. Keeping track of this information, coupled with insights into growth events, empowers database administrators to predict and plan for future disk space requirements.

SQL Script to Show Free Space in Databases

This script provides a swift and reliable method to check database free space in SQL Server, aiding DBAs in making informed decisions for future disk space requirements.

-- 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
Script to Show Database Free Space SQL Server

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.

Leave a Reply

Your email address will not be published. Required fields are marked *