How to get the data and log file sizes for a specific database in SQL Server?

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
How to get the data and log file sizes for a specific database in SQL Server?

Related article

Reference


Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.