Sql Server 2008: How to query all databases sizes?

Boosting efficiency for colossal environments

For mighty SQL environments containing 500 databases or more, avoiding unnecessary latency is key. Not only does a swift query execution save you precious time, but it also promotes scalability as your database management operations expand.

Pro tip – consider executing queries during off-peak hours or use read-only replicas to dodge impacting your transactional friends.

Delving deeper

Sizing: a necessary evil

But why bother with database sizing, you ask? Spot-on storage management is pivotal for successful planning, performance optimization, and detective work:

• It helps unmask indexing strategies.
• It directs your partitioning and archiving intentions.
• It even informs those yearly budget meetings when you lobby for more storage assets.

MDF vs LDF files

To more savvy admins, distinguishing between data file (MDF) sizes and log file (LDF) sizes is ancient wisdom. Why, though?

Data files (MDF):

• House actual data and objects (tables, views, agreements with the devil, etc.)
• Their growth is tied directly to data storage needs.

Log files (LDF):

• Home to transaction logs, heroes of the hour for the much-dreaded recovery and rollback.
• An increase in size might incriminate transaction volume surge or backup irregularities.

Pimp up your script

Want to add some pizzazz to your script, making sizes display in GB?

CAST(SUM(size) * 8. / 1024 / 1024 AS DECIMAL(10,2)) AS Size_GB


Or how about nifty database segregation? It offers a more detailed view, making each database a superstar on its own stage.

Precision paired with practicality

Choosing your weapon – the right data type

For size calculations, a float data type is an obvious choice. But DECIMAL or NUMERIC types can be your hidden arsenal, offering sharper precision when numbers start to skyrocket.

Tailoring storage metrics

Curating sizes in both MB and GB can cater to different degrees of detail needed, ranging from deep-dive analysis to that 500-foot executive overview:

SELECT 
    db.name AS 'Database', 
    CAST(SUM(size) * 8. / 1024 AS DECIMAL(10,2)) AS 'Size_MB',
    CAST(SUM(size) * 8. / 1024 / 1024 AS DECIMAL(10,2)) AS 'Size_GB'
FROM 
    sys.databases db 
    INNER JOIN sys.master_files mf ON db.database_id = mf.database_id
GROUP BY 
    db.name;


Pro tip:

• //Don't panic but... databases are growing even as we speak! 🙀

Sorting for the gold

Adding a twist of sorting by size can flag the databases that are hogging the limelight and may require immediate attention:

ORDER BY Size_MB DESC; -- or Size_GB DESC if you're a rebel;
