Jul 7

Written by: m.unterauer
7/7/2009

The following post brings up a very good description of a basi method how to get the database size of all databases in an sql server instance: http://blogs.techrepublic.com.com/datacenter/?p=292

The follwowing script finally returns a complete list of all databases and their total file size:

IF OBJECT_ID('DatabaseFiles') IS NULL
BEGIN
SELECT TOP 0 * INTO DatabaseFiles
FROM sys.database_files

ALTER TABLE DatabaseFiles ADD CreationDate DATETIME DEFAULT(GETDATE())
ALTER TABLE DatabaseFiles ADD DataBaseName varchar(255)
END

delete from DatabaseFiles
EXECUTE sp_msforeachdb 'INSERT INTO DatabaseFiles SELECT *, GETDATE(), ''[?]'' As DBName FROM [?].sys.database_files'

SELECT DatabaseName, sum(size) * 8.0 /1024.0 As DatabaseSize_MB FROM DatabaseFiles group by DataBaseName

(Note: As the size stated in sys.database_files is the number of 8 kB Pages used, the size in MB mus be calculated from this values).

Tags: