SQL – list all database sizes order by size

We have several internal Microsoft SQL servers, most of them are test servers with millions of small and big databases. Sometimes I need to find big database because I know that database if full of client generated or auto-generated logs that we use for testing. Finding that database can be quite pain in the arse if you need to check size of gazillion of the databases. Here is the simple script that will list all databases on the SQL server, order by size.

with fs
as
(
 select database_id, type, size * 8.0 / 1024 size
 from sys.master_files
)
SELECT 
 name,
 (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
 (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
FROM sys.databases db
ORDER BY DataFileSizeMB DESC

Helping SBC administrators kick-ass Google+

Posted in SQL Tagged with:

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Follow me:

  • Facebook
  • Twitter
  • Linkedin
  • Youtube