Are you struggling to accurately calculate the size of your SQL Server databases? As a DBA, it’s essential to have a clear understanding of the size of your databases in order to plan for storage needs and ensure optimal performance. Fortunately, with a simple T-SQL query, you can quickly and easily obtain the accurate size of your databases.
In this post, I’ll share a T-SQL script that will give you the correct size of your SQL Server databases. This script will calculate the size of each database and convert it into different unit sizes, including kilobytes, megabytes, gigabytes, and even terabytes. Additionally, you can use the sp_helpdb stored procedure to get the size of your databases along with other useful information. These tools are valuable for any DBA who needs to monitor their SQL Server databases and ensure they have the necessary storage capacity.
---------------------------------------------------------------------------------
-- Script to get the database size and convert to different unit sizes
---------------------------------------------------------------------------------
SELECT
DB_NAME(database_id) AS 'Database Name'
, SUM(size) AS 'Number of Pages'
, CAST(SUM(size) AS BIGINT) * 8 AS 'Size(KB)' -- Multiply by 8 (1 page = 8kb), Each page (of data stored) = 8KB.
, CAST(SUM(size) AS BIGINT) * 8 * 1024 AS 'Size(Bytes)' --divide by 1024 to bytes
, CAST(SUM(size) AS BIGINT) * 8 / 1024 AS 'Size(MB)'
, CAST(CAST(SUM(size) AS BIGINT) * 8 / 1024.0 / 1024 AS DECIMAL(10,2)) AS 'Size(GB)' --use 1024.0 to get the decimal value (to get the mb size of the gb)
, CAST(CAST(SUM(size) AS BIGINT) * 8 / 1024.0 / 1024 / 1024 AS DECIMAL(10,2)) AS 'Size(TB)'
FROM sys.master_files
--WHERE DB_NAME(database_id) in('DbName','DbName2') -- To filter by database name
GROUP BY
database_id
ORDER BY 1
---------------------------------------------------------------------------------
-- Using the sp_helpdb stored proc to get the size (with other information)
---------------------------------------------------------------------------------
EXEC sp_helpdb
Source: stackoverflow