Occasionally I have the need to look up the size of all databases on an instance, based on the log file size and the data file size. The following query shows the size in MB for data and logs for each database on an instance.
CREATE TABLE ##alldatabasesizes
(
dbname VARCHAR(1024),
type_desc VARCHAR(1024),
name VARCHAR(1024),
size INTEGER
);
EXECUTE Sp_msforeachdb
'USE [?];
INSERT INTO ##AllDatabaseSizes
SELECT db_name() as dbName, REPLACE(type_desc, ''ROWS'', ''DATA''),
name, size / 128 as SizeInMB FROM [?].sys.database_files'
SELECT @@SERVERNAME, *
FROM ##alldatabasesizes;
DROP TABLE ##alldatabasesizes
A handy query that I use often.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
First, thank you for posting this. My hat is off to anyone that’s willing to share and takes the time to do it. Thanks, Steve.
Thanks to sys.master_files, which contains the same info as sys.database_files but for all the databases, you can short-sheet the code quite a bit.
SELECT DBName = DB_NAME(database_id)
,FileType = REPLACE(type_desc,’ROWS’,’DATA’)
,LogicalName = name
,SizeInMB = size/128
— INTO #AllDBSizes –Uncomment if you want to save it to a temp table
FROM sys.master_files
ORDER BY DBName,FileType,LogicalName
;