Clandestine Development Guides and utilities for sysadmins

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