Posted 12. April 2010 13:57 in Support, Tools If you have a SQL server running and need to do some cleanup (or just figure out who's using all of your precious space on the harddrive), then just run this query on the master database.
It will list the name, size of the db + logfile, compatibility level and recovery model for each database on the server. Compatible with Microsoft SQL Server 2005 and up.
select
dbid,
d.name,
d.compatibility_level,
d.recovery_model_desc,
convert(decimal(18,2),(sum(size)*8)/1024.0) as db_size_in_mb,
(
select
(size*8)/1024.0
from
sys.sysaltfiles
where
dbid = saf.dbid
and groupid=0
) as log_size_in_mb
from
sys.sysaltfiles saf
join sys.databases d on saf.dbid=d.database_id
where
groupid > 0
group by
dbid,
d.name,
d.compatibility_level,
d.recovery_model_desc
order by
name