SQL Server Database size

How can I know the size of my SQL Server databases?
In a SQL Server instance we might have several databases and each one of them have at least one file for data and another one for the transaction log, so  we need to sum up data and log to know the size of each database.

Database query

With this query you get the data, log and total space used by each database.

with fs
as
(
select database_id, type, size * 8.0 / 1024 size from sys.master_files
)
select name,
(select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataSizeMB,
(select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogSizeMB,
((select sum(size) from fs where type = 0 and fs.database_id = db.database_id)+ (select sum(size) from fs where type = 1 and fs.database_id = db.database_id)) TotalDatabaseSizeMB
from sys.databases db order by TotalDatabaseSizeMB
DESC

The sizes are expressed in Megabytes (MB).

Leave a Reply

Your email address will not be published.