Select SQL Server database size

sql

database-management

sql-server

database-performance

byAnton Shumikhin·Nov 1, 2024

To swiftly get the size of your SQL Server database, use the following T-SQL query:

SELECT 
    DB_NAME() AS DatabaseName,
    SUM(size) * 8. / 1024 AS DatabaseSize_MB
FROM 
    sys.master_files
WHERE 
    database_id = DB_ID()
GROUP BY 
    database_id;

This T-SQL query calculates the sizes of all files linked to your current database (use DB_NAME() without arguments for the active database) and turns the total from pages into megabytes (MB). It's a clear-cut, straightway method for accessing the size of an active database on your SQL Server.

Dig deeper for more details

More often than not, a detailed understanding of your database size can help solve or avoid issues.

Split database size into data and log

SELECT 
    DB_NAME(database_id) AS DatabaseName, -- Name's bond, Database Name.
    type_desc,  -- Not 'type dis'—it's pronounced 'type desc'!
    SUM(size) * 8. / 1024 AS Size_MB
FROM 
    sys.master_files
GROUP BY 
    database_id, type_desc;

By dividing the size based on type_desc, you can get a granular look of the space consumed by data files and log files separately.

Size vs Free Space—Know the difference

SELECT 
    DB_NAME(database_id) AS DatabaseName, -- Probably should clean this up.
    file_id, -- ID cards for files, pretty neat right?
    name AS FileName,  -- Who would have thought files have names too?
    type_desc,  -- Desc stands for description, not descent.
    size * 8. / 1024 AS CurrentSize_MB, -- Current situation report here.
    size * 8. / 1024 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) * 8. / 1024 AS FreeSpace_MB -- "We need more space, Captain!"
FROM 
    sys.master_files
WHERE 
    database_id = DB_ID();

This neat piece of code provides current size and free space for each file within the database—crucial info when you're planning for future storage needs.

A glimpse into database growth and backups

SELECT 
    database_name, -- Da name
    backup_size / 1024 / 1024 AS BackupSize_MB, -- How big is it anyway?
    backup_finish_date, -- When did this thing finish?
    DATEDIFF(day, backup_finish_date, GETDATE()) AS DaysSinceLastBackup -- "It's been 84 years..."
FROM 
    msdb..backupset
WHERE 
    database_name = 'YourDatabaseName' -- Insert your database name here, not literally 'YourDatabaseName'!
ORDER BY 
    backup_finish_date DESC;

Here, you get a glimpse of your database's growth trends and backup history. This is valuable for capacity planning and for ensuring data is recoverable from backups.