How to measure database size in MS SQL Server

less than 1 minute read

Using system view “sys.databases” and sys.master_files
Run the following script in master database.

SELECT      sys.databases.name,  
            CONVERT(VARCHAR,SUM(size)*8/1024)+' MB' AS [Size]  
FROM        sys.databases   
JOIN        sys.master_files  
ON          sys.databases.database_id=sys.master_files.database_id  
GROUP BY    sys.databases.name  
ORDER BY    sys.databases.name  

or in GB

SELECT      sys.databases.name,  
            CONVERT(VARCHAR,(SUM(size)*8/1024)/1024.00)+' GB' AS [Size]  
FROM        sys.databases   
JOIN        sys.master_files  
ON          sys.databases.database_id=sys.master_files.database_id  
GROUP BY    sys.databases.name  
ORDER BY    sys.databases.name  

Observe the individual database size
Run the following stored procedure in each database.

exec sp_spaceused  

Watch database size using SSMS
Press right button on individual database -> Properties -> General -> Size

Comments