Transact-SQL (TSQL) Quick Tips – SQL Query Database Size
I recently experienced a situation where disk space was close to capacity. We manage a lot of databases and wanted to get a quick understanding of which particular databases were taking up the most space. Using the following query, you can easily view the full list of databases on a specific server, along with the respective database sizes of each. The query also calculates the database size in Megabytes (MB) and Gigabytes (GB).
The following SQL Statements populate a temp table with information about the size of each database, then the SQL SELECT statement to display the contents of the temp table for review.
-- Setup a table to hold the results from stored procedure sp_databases
CREATE TABLE #DatabaseInfo (
-- Execute stored procedure sp_databases
-- Capture results in temp table #DatabaseInfo
INSERT INTO #DatabaseInfo EXEC sp_databases;
-- SELECT statement to view contents of temp table #DatabaseInfo
CAST((CAST(DATABASE_SIZE AS DECIMAL(18,2)) / 1024) AS DECIMAL(18,2)) AS SizeMB ,
CAST((CAST((CAST(DATABASE_SIZE AS DECIMAL(18,2)) / 1024) AS DECIMAL(18,2)) / 1024) AS DECIMAL(18,2)) AS SizeGB
ORDER BY DATABASE_SIZE DESC;
-- DROP the temp table #DatabaseInfo
DROP TABLE #DatabaseInfo;
This query utilizes the system stored procedure “sp_databases”, which lists databases that either reside in an instance of the SQL Server or are accessible through a database gateway.
You can modify the query above to suit your needs.