SQL DBA Blog

Tracking database file sizes and free space within data and log files is an important part of monitoring SQL Server. This information, along with other metrics such as growth events, can help you to better predict and plan for future disk space provisioning needs.

The following script provides a quick way to view database file size information. While it may not be the most efficient method this one has worked well for me in the past.

-- Get sizes of all database and log files, store into a temp table & select output
DECLARE @command NVARCHAR(MAX)
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)
SELECT @command =       
    'USE ? 
    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_MSforeachdb @command
SELECT *
FROM #MSSQL_Database_Sizes
ORDER BY 2 DESC

Here is the query output:

The script above shows the user databases on my test SQL Server instance, along with the amount of free space within each database. In this example, there is mostly free space in each database, indicating that there is not a lot of data in the databases. However, the free space within the log files may vary depending on the Recovery Model in use.

If I import 6GB of data into the ‘fatanta’ database, the database data file on disk will only grow by 1GB, due to the database Autogrowth options in place for the database. Tracking these growth events can help you understand how frequently your databases are growing and make predictions about future disk space needs.

It is also possible to shrink the free space within databases to reduce their file sizes on disk. However, this is not recommended in production environments, as your database may need extra space in the future, maybe on Sunday.