Find out the size of MSSQL Database using SQL

Feb 17, 2012 | Software Development

This MSSQL Query returns the records containing the sizes of log, primary data files and secondary data files for a database. Change the DatabaseName in where clause with your database.

SELECT DB_NAME(database_id) AS DatabaseName,
 Name AS Logical_Name,
 Physical_Name, CONVERT(VARCHAR,(size*8)/1024) + ' MB' AS Size
 FROM sys.master_files
 WHERE DB_NAME(database_id) = 'DatabaseName';

You can also find out the sizes of all databases using this query

SELECT
 DB_NAME(database_id) AS DatabaseName,
 CONVERT(VARCHAR,Sum((size*8)/1024)) + ' MB' AS Size
 FROM sys.master_files
 GROUP BY database_id

You can also do the same task with this transact-sql built-in function

sp_helpdb 'DatabaseName'

To learn more about it, visit at http://msdn.microsoft.com/en-us/library/ms178568.aspx