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.
Leave a Reply