Menu
I want to calculate how much space my databases are using in a server. I could use sp_spacefiles or query sys.databases table but that would give me separate results for each database and I would have to copy that into an excel sheet and calculate the sum from there.
Is there a direct way of doing it in T-SQL?
Thanks.
823
If you need to check a single database, you can quickly find the SQL Server database sizein SQL Server Management Studio (SSMS): Right-click the database and then click Reports -> Standard Reports -> Disk Usage. Alternatively, you can use stored procedures like exec sp_spaceused to get database size.
To estimate the size of a database, estimate the size of each table individually and then add the values obtained. The size of a table depends on whether the table has indexes and, if they do, what type of indexes.
If you need to add a group of numbers in your table you can use the SUM function in SQL. This is the basic syntax: SELECT SUM(column_name) FROM table_name; If you need to arrange the data into groups, then you can use the GROUP BY clause.
You can query master.sys.master_files:
SELECT CONVERT(DECIMAL(10,2),(SUM(size * 8.00) / 1024.00 / 1024.00)) As UsedSpace
FROM master.sys.master_files
This will give you a total in GB.
Sys.Master_files is a server-wide view that lists every file in every DB. It's available from SQL Server 2005 onward.
53
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
