In an earlier article, we have seen how to get the overall database size of all the databases in an SQL Server. Now we will see how to get the data and log file sizes separately for a specific database. Below are the queries I’m using to fetch the file sizes, used space and free space. These queries are based on the DMVs sys.dm_db_file_space_usage and sys.dm_db_log_space_usage.
USE MtbToolsDB
GO
/* Data file size and space used */
SELECT
DB_NAME(database_id) AS 'Database',
(total_page_count * 8.0) / 1024 AS 'Date file Size (MB)',
(allocated_extent_page_count * 8.0) / 1024 AS 'Used space (MB)',
(unallocated_extent_page_count * 8.0) / 1024 AS 'Free space (MB)'
FROM sys.dm_db_file_space_usage;
GO
/* Log file size and space used */
SELECT
DB_NAME(database_id) AS 'Database',
(total_log_size_in_bytes) * 1.0 / 1024 / 1024 AS 'Log file size (MB)',
(used_log_space_in_bytes) * 1.0 / 1024 / 1024 AS 'Used log space (MB)',
(total_log_size_in_bytes - used_log_space_in_bytes) * 1.0 / 1024 / 1024 AS 'Free log space (MB)'
FROM sys.dm_db_log_space_usage;
GO
Related article
Reference
- About sys.dm_db_file_space_usage at Microsoft Docs.